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?

    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?


  • 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

    Is that the entire code? there's alot of Declarations missing? please could you post the whole code if thats the case

  • Re: Error Selecting Sheet In Opened Workbook

    Are you trying to use codename references to a sheet that is not in the workbook with the code in? If so I don't think you can.

    And rather than using the indexes to reference workbooks why not assign them to variables to avoid any potential confusion.

    Dim wbkSite as workbook

    Set wbkSite = Workbooks.Open(Filename:=Sitefile)


  • Re: Error Selecting Sheet In Opened Workbook

    Play around with this...

  • 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!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!