+ Reply to Thread
Results 1 to 13 of 13

copy all sheets from one workbook to the workbook running the macro

  1. #1
    Registered User
    Join Date
    02-26-2012
    Location
    Pavlos Pavlou
    MS-Off Ver
    Excel 2011
    Posts
    21

    copy all sheets from one workbook to the workbook running the macro

    Hi All,

    I am having an issue and keep getting the same error on all the codes I come up with for this problem.

    Basically I am trying to write a macro that will copy all the sheets from a named workbook and transfer them to the workbook I am running the macro in, I have tried using ActiveWorkbook, ThisWorkbook and defining and naming the workbook I am currently running the macro in. I keep getting
    Run-time error '9'
    Subscript out of range

    Here is my current code.

    Please Login or Register  to view this content.
    If anyone could amend/suggest another code to do what I am looking for that would be great guys!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: copy all sheets from one workbook to the workbook running the macro

    Try this, it will copy all the worksheets from TrianglesWB to the workbook the code is in, ThisWorkbook
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    02-26-2012
    Location
    Pavlos Pavlou
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: copy all sheets from one workbook to the workbook running the macro

    Thanks for the reply Norie!

    Unfortunately I am still getting a run-time error! I really dont understand why

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    What exactly did you try?

    Also which line causes the error?
    Last edited by Norie; 12-19-2012 at 08:42 AM.

  5. #5
    Registered User
    Join Date
    02-26-2012
    Location
    Pavlos Pavlou
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: copy all sheets from one workbook to the workbook running the macro

    Hi Norie,

    I used the modified code

    Please Login or Register  to view this content.
    The line Set TrianglesWB = Workbooks("Triangles_All.xls") seems to be causing the error

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: copy all sheets from one workbook to the workbook running the macro

    Do you have a workbook called 'Triangles_All.xls' open when you run the code?

    If you don't, or the name is even slightly out, then you would get the error you decribe.

  7. #7
    Registered User
    Join Date
    02-26-2012
    Location
    Pavlos Pavlou
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: copy all sheets from one workbook to the workbook running the macro

    Hi Norie,

    Thanks for your code it does do what Im looking for! I was stupidly not opening the file in the same excel window and it wasnt working! Well its all a learning experience haha

    Im going to be cheeky and ask one more thing if I may, is there any way to get the sheets to copy in after the sheet I already have in the target workbook? They currently paste in before it. Basically I have one control sheet which does all the work and I want the other sheets to paste in after that sheet if possible?

    Thanks again! Much appreciated!

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: copy all sheets from one workbook to the workbook running the macro

    The code I posted should put the copied sheet after all the existing sheets in the target workbook.

  9. #9
    Registered User
    Join Date
    02-26-2012
    Location
    Pavlos Pavlou
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: copy all sheets from one workbook to the workbook running the macro

    It seems to put them before and Im not sure what part of the code could be amended to rectify this?

    Thanks for any help

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Can you post the exact code you are using?

  11. #11
    Registered User
    Join Date
    02-26-2012
    Location
    Pavlos Pavlou
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: copy all sheets from one workbook to the workbook running the macro

    Hi Norie,

    The code is :

    Please Login or Register  to view this content.

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: copy all sheets from one workbook to the workbook running the macro

    Try this.
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    02-26-2012
    Location
    Pavlos Pavlou
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: copy all sheets from one workbook to the workbook running the macro

    Hi Norie!

    Hope you had a good Christmas!

    That works perfectly, thank you very much for the amendment!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1