Error Selecting Sheet In Opened Workbook

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?

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?

James

I think if possible i'd prefer to do it using the workbooks(1) method as I will be expanding this macro to use other workbooks and so I could just copy the code over rather than editing the workbook name.

But is this what's causing the problem with the selection of the sheet by its codename?

Re: Error Selecting Sheet In Opened Workbook

Hi Guys, Your suggestions and code worked great!!

Thanks ever so much. I'm having a few minor problems converting it to suite my workbook aand so have posted a couple of new threads but the help has been really great!

Thanks for your time!

James