Hi All
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?
Code
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
Display More
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?
James