the following is my code that matches header between two workbooks and copies the data.
Set Wbk = Workbooks.Open("D:\data files of Roshan\Global_HEADCOUNT.xls") '<-------Set Path of the global headcount file > Worksheets("GLOBAL_HEADCOUNT").Select '<-------Set the sheet name of the head count file> Mastcol = Cells(1, Columns.count).End(xlToLeft).Column j = Cells(Rows.count, 1).End(xlUp).Row Set mastRng = Range(Cells(1, 1), Cells(1, Mastcol)) b = mastRng For k = 1 To lastCol For n = 1 To Mastcol If UCase(a(3, k)) = UCase(b(1, n)) Then Windows("Global_HEADCOUNT").Activate '<-----Set the name of the global headcount file> Worksheets("GLOBAL_HEADCOUNT").Range(Cells(2, n), Cells(j, n)).Copy '<------Set the name of the sheet in the global headcount file> Windows("RMG Dashboard.xlsm").Activate '<------Set name of the dashboard file> Worksheets("Dashboard").Select '<------Set name of the sheet in the dashboard file> Cells(i + 1, k).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Exit For End If Next Next Call Wbk.Close(False)
as you can see the file path of the second file must be set evertime i want to work with a different file. People keep tampering with the code by accident so i want to eliminate this way and chose the dialog box option. global headcount is what i want to select using a dialog box, RMG dashboard is my file with dashboard worksheet
i have something like the following to start but it is incomplete.. any help?
'The folder containing the files to be recap'd Set fd = Application.FileDialog(msoFileDialogFilePicker) fd.InitialFileName = "G:\Work\" '<----- Change this to where the files are stored. fd.InitialView = msoFileDialogViewList 'allow multiple file selection fd.AllowMultiSelect = True fd.Filters.Add "Excel Files", "*.xls*" filechosen = fd.Show 'Create a workbook for the recap report Set Master = ThisWorkbook If filechosen = -1 Then