+ Reply to Thread
Results 1 to 8 of 8

VBA to copy range from one workbook and paste to another open workbook

  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    Oregon
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    47

    VBA to copy range from one workbook and paste to another open workbook

    Hello,

    I've been searching the forums and haven't found just what I need. I should mention that I'm not very skilled in VBA, but have lots of experience tweaking other people’s code for my own use.

    I have a Global (source) workbook which contains data on the "Checks" sheet (data range is A5:V). I need to be able to copy that range and paste into multiple other Regional (destination) workbooks in range A2:V. I will have three regional workbooks. The "Checks" sheet in the Regional files will be hidden. I'd like the copy/paste to occur whenever the Regional workbooks are opened. I'd be fine using a macro button, if that is easier.

    I’d like the entire contents of the “Checks” range to be pasted over any existing data in the Regional files’ Checks tab (basically refreshing the data each time).

    When users open the Regional files, the Global file may/may not already be open. Each file is in the .xlsm format with a password. I’ve attached sample GlobalSource and RegionalDestination workbooks (password is set to “password”).

    Here is my process
    1. User updates the Checks tab in the GlobalSource file. Saves file. May/may not close file.
    2. User opens RegionalDestination file. Checks tab is refreshed (should match the data in the Global file)

    Any help would be greatly appreciated!
    Attached Files Attached Files
    Last edited by lilvictorians; 02-20-2013 at 04:02 PM. Reason: added note about hidden sheet

  2. #2
    Registered User
    Join Date
    07-05-2012
    Location
    Oregon
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    47

    Re: VBA to copy range from one workbook and paste to another open workbook

    Surely this hasn't stumped the board gurus?!

  3. #3
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: VBA to copy range from one workbook and paste to another open workbook

    Do you want the RegionalDestination files to be opened and then updated, 1 by 1 just after the Global file is saved?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Registered User
    Join Date
    07-05-2012
    Location
    Oregon
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    47

    Re: VBA to copy range from one workbook and paste to another open workbook

    arlu1201 - That's a good question, and I'm not sure of the best answer. I'd like the Regional files to display the updated Checks info whenever a user opens those files, but there are many times when users may have any of the Regional files or the Global file open, so I need to be sure that the updating of the Checks sheet doesn't interfere with other tasks.

    I'm not opposed to having a button in the Regional files to trigger the update, if it makes sense to that. I'm open to any ideas.

    Thank you.

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: VBA to copy range from one workbook and paste to another open workbook

    Maybe you can have the Regional files closed at the time of the update?

  6. #6
    Registered User
    Join Date
    07-05-2012
    Location
    Oregon
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    47

    Re: VBA to copy range from one workbook and paste to another open workbook

    If that is the only (or best) way, then I'd be fine having a button in the Global file to trigger the updates to the Regional files. Then I could be certain that all the regional files are closed prior to doing the update.
    Last edited by lilvictorians; 02-24-2013 at 01:48 PM.

  7. #7
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: VBA to copy range from one workbook and paste to another open workbook

    Its not the only way as there could be other ways which i am not aware of. Its just one of the easier ways to work with, with minimum issues.

  8. #8
    Registered User
    Join Date
    07-05-2012
    Location
    Oregon
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    47

    Re: VBA to copy range from one workbook and paste to another open workbook

    I think arlu1201’s idea should work just fine. If anyone can provide some code examples, I'd sure appreciate it. Thank you!

+ 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