+ Reply to Thread
Results 1 to 5 of 5

Copying worksheet from another open workbook using variables, paste to active workbook

  1. #1
    Registered User
    Join Date
    11-04-2009
    Location
    new zealand
    MS-Off Ver
    Excel 2007
    Posts
    19

    Copying worksheet from another open workbook using variables, paste to active workbook

    hi,

    i have spent far too long on this and its making me go crazy. I've made many attempts using different methods but the below code probably shows exactly what i want to do. I've tried "setting" the workbook name but if its already open it asks if i want to reopen it and wipe unsaved data.

    anyway, i have two workbooks(4 really, but repeating steps should be fine). I need to copy the first sheet from a workbook named on today's date(which is always open, "thestring" in my code), and then paste it into the active workbook. if im not using variables it works, or i use these variables just to open the workbook, it works.

    i get run time error '9', subscript is out of range. am i using the wrong code? or am i naming the workbook incorrectly?

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Copying worksheet from another open workbook using variables, paste to active workbook

    try this

    Please Login or Register  to view this content.
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    11-04-2009
    Location
    new zealand
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Copying worksheet from another open workbook using variables, paste to active workbook

    that works, and thanks a lot for your help but i was trying to include the full name to ensure if the copy workbook was saved in a different folder to the "testingclear.xlsm" it would still copy(sorry, in hindsight i didn't make that very clear). am i doing it wrong if i get the error when i move the file to a different sheet? or does this method only work if they are both in the same folder?

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Copying worksheet from another open workbook using variables, paste to active workbook

    You need path only for opening/saving operations, when the wb is already open the wb name has not the path included, you can see the name on the top panel

  5. #5
    Registered User
    Join Date
    11-04-2009
    Location
    new zealand
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Copying worksheet from another open workbook using variables, paste to active workbook

    thanks a lot for your help, i foolishly assumed you had to give the full file name when using workbook names, even when they are open. i assume this is part of the reason that you can only have one workbook open when they have the same names, even if they are saved in different locations.
    thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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