I have a main workbook that uses a macro to open a workbook provided to me from another department. This workbook contains a sheet that is basically a copy of the sheet in the main document and has the same sheetname and same codename.
The macro should copy the sheet data from the site workbook to the sheet in the main workbook. However, when i tell the workbook to select the sheet in the site file using its codename it errors.
I cant understand it. Does anyone have any ideas?
Sub Consolidate_Bmth() ' Sitefile = "\Weekly File " & _ Format(Weekly_Main.Range("I1").Value, "dd_mm_yyyy") & " Site.xls" Workbooks.Open Filename:=Sitefile Workbooks(2).Activate 'Copy from Sitefile Weekly_Bmth.Select 'WHY Wont it select this sheet?************* Cells.Select Selection.Copy Workbooks(1).Activate 'Paste to Main File Weekly_Bmth.Select Cells.Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("B4:I4").Select Workbooks(2).Activate End Sub
Is this caused by the codenames being the same in both workbooks(1) and (2)? I don't really understand because if i'm in the newly opened workbook, why does it error when I select the sheet i want?