hello all the above code matches header between two sheets and if they match copies the data......
now the problem with this is that i have to hard code the file names always, which may cause someone to change the data in my code later so i want to avoid this approach and use the file open dialog box.
now the lone for using a file open box has been added but as you can see the file names are still hardcoded.... now what do i replace them with?
in the above example dashoard is my main workbook and the sheet name...
global headcount is then name of the file and sheet which i want to take the data from.
Code
'lastCol = Worksheets("Dashboard").Cells(3, Columns.Count).End(xlToLeft).Column
lastCol = 15
lastrow = Worksheets("Dashboard").Cells(Rows.Count, 1).End(xlUp).Row
Set cmpRng = Range(Cells(1, 1), Cells(3, lastCol))
a = cmpRng
i = Cells(Rows.Count, 1).End(xlUp).Row
fn = Application.GetOpenFilename("Excel,*.xls*")
If fn = "False" Then exit Sub
Set Wbk = Workbooks.Open(fn)
Worksheets("GLOBAL_HEADCOUNT").Select
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
Worksheets("GLOBAL_HEADCOUNT").Range(Cells(2, n), Cells(j, n)).Copy
Windows("Dashboard_for_Roshan.xlsm").Activate
Worksheets("Dashboard").Select
Cells(i + 1, k).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Exit For
End If
Next
Next
Call Wbk.Close(False)
Display More