79168641

Date: 2024-11-08 02:53:03
Score: 11.5 🚩
Natty: 4.5
Report link

Guys i written a code for merging file but i want to re modify it but don't have proper idea can anyone help me?

Sub Master_File()
Dim n As Integer
Dim wb As Integer
Dim master As String
Dim userfile As String
'Dim l_Row As Long
'Dim l_Dist As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

master = ThisWorkbook.Name

With Application.FileDialog(msoFileDialogOpen)
                .AllowMultiSelect = True
                .Title = "Locate Your Files"
                .Show
                
            n = .SelectedItems.Count
            
            For wb = 1 To n
            Path = .SelectedItems(wb)
            Workbooks.Open (Path)
            
            userfile = ActiveWorkbook.Name
            
            For Each Sheet In ActiveWorkbook.Worksheets
            If Sheet.Name = "Tracker" Then
            Sheet.Select
            Sheets("Tracker").Select
            Range("a1").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            'l_Row = Sheets("Tracker").Range("A1048576").End(xlUp).Row
            'This will find the last row of the tracker sheet
            'Range("A2:K" & l_Row).Copy
            'This code will copy all dat from tracker sheet
            Windows(master).Activate
            'This code will activate the master file where we will paste our data
            Sheets("Sheet1").Select
            Range("a1").Select
            'Range("a1048576").Select
            Selection.End(xlDown).Select
            Selection.End(xlDown).Select
            Selection.End(xlUp).Select
            ActiveCell.Offset(1, 0).Select
            
            'l_Dist = Sheets("Sheet1").Range("A1048576").End(xlUp).Row + 1
            'This code will find the next  blank row in the master file
            'Sheets("Sheet1").Range("A" & l_Dist).Select
            'This code will find the last non blank cell in the master file
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Range("b1048576").Select
            Selection.End(xlUp).Select
            Selection.End(xlToLeft).Select
            ActiveCell.Offset(1, 0).Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Delete
            
            Range("a2").Select
            Selection.AutoFilter
            ActiveSheet.Range("$A$2:$L$41").AutoFilter Field:=1, Criteria1:= _
            "Employee Number"
            ActiveCell.Offset(20, 0).Range("A1").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.EntireRow.Delete
            ActiveCell.Offset(-20, 0).Range("A1").Select
            Selection.AutoFilter
            Range("a2").Select
            
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Font.Bold = True
            Selection.Columns.AutoFit
            With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.Select
    
            
            Windows(userfile).Close savechanges:=False
            
            End If
        Next Sheet
    Next wb
    
End With

End Sub

In this code i want to edit at here like i want to connect the sheet name from cell A1 like what i write in cell A1 that will be consider as sheet name from other workbooks for find the sheet and then it will find that name from other workbook sheets if i write in cell A1 = Company then it will find Company sheet name from other workbook sheets

**

If Sheet.Name = "Tracker" Then
            Sheet.Select
            Sheets("Tracker").Select
            Range("a1").Select

**

Can anyone help me Please?

Reasons:
  • Blacklisted phrase (1): help me
  • RegEx Blacklisted phrase (3): can anyone help me
  • RegEx Blacklisted phrase (3): Can anyone help me
  • RegEx Blacklisted phrase (1): i want
  • RegEx Blacklisted phrase (2): help me Please
  • Long answer (-1):
  • Has code block (-0.5):
  • Ends in question mark (2):
  • Low reputation (1):
Posted by: Rahul Vishwakarma