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?