+ Reply to Thread
Results 1 to 13 of 13

Copy Numerous Sheets to Numerous Unopened Workbooks

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    302

    Copy Numerous Sheets to Numerous Unopened Workbooks

    I have a tab/sheet of raw data (called "Data") that I refresh each month. State is one of the columns in said sheet.

    I have a macro that then parses the data from the "Data" tab into individual tabs/sheets within that same workbook (called "Parse_Data"). So, once the macro runs I have 40+ tabs in that same workbook, each of them containing an individual state's data. Each of these new tabs is named automatically based on the state data contained in each (AL, AZ, CA, etc.).

    So...I have a group of separate workbooks/files, each of which is a financial model ("AZ_Model", "CA_Model", etc) that uses the data of an individual state that I described above.

    The data updates each month. This is no issue in the "Parse_Data" file. I replace the raw data and run my macro.

    What I would like to do is create a macro that then takes the "Parse_Data" file and copies each new state-named tab to the proper state model, while replacing the old tab of the same name.

    In other words, once I run the macro for the Parse_Data file and I have my new state tabs, I would like the "AZ" tab for example to find the "AZ_Model" file and replace the existing "AZ" tab found in the AZ model. This would obviously make updating 40+ models a lot easier.

    Any help is appreciated.

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks

    Assuming that the 'Parse_Data' file is in the same directory as the state model files and tha 'Parse_Data' will host the code.
    Please Login or Register  to view this content.
    I strongly suggest you test this on a copy or mock-up of your files befor applying it to the original.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Contributor
    Join Date
    10-24-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    302
    Thank you...is there a way to execute this macro without it opening each state file? It seems to want to open each file prior to pasting in the new sheet.

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks

    There is no way to tell which states were updated from the info in the OP, so each sheet is addressed to be certain that all changes are included. If you mean can it be done without opening the files, then the answer is that type of programming is beyond my skills.
    Last edited by JLGWhiz; 04-22-2018 at 03:48 PM.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,601

    Lightbulb

    Quote Originally Posted by hchavous View Post
    Thank you...is there a way to execute this macro without it opening each state file? It seems to want to open each file prior to pasting in the new sheet.
    If you prefer to not see the opening of a workbook just use GetObject function instead of Workbooks.Open method

  6. #6
    Forum Contributor
    Join Date
    10-24-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    302

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks

    So it's as simple as replacing Workbooks.Open with GetObject? I'm trying to avoid lengthy trial-and-error with syntax.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,601

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks


    Yes as you can see in VBA inner help but as you save the workbooks avoid GetObject (sorry I didn't see that before)
    'cause your workbooks will be saved hidden !

    Better is to desactivate the display via ScreenUpdating property (to see in VBA inner help) …
    Last edited by Marc L; 04-23-2018 at 05:33 AM.

  8. #8
    Forum Contributor
    Join Date
    10-24-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    302

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks

    Ok, what would the code then look like if I were to update with ScreenUpdating?

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,601

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks


    Just add to your initial code a line before the loop to desativate the display
    and another one after the loop in order to reactivate it …

  10. #10
    Forum Contributor
    Join Date
    10-24-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    302

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks

    Ok, that works (many thanks) but now I have another problem...

    When it goes to replace the state tab in a given state file, if that tab already exists, I get a "name already taken" error. The whole point of this exercise is to replace the tab in a given file each month. In other words, if a tab called, "MT" exists in the MT_Model file, it errors and says the name is already taken. Any ideas anyone?

  11. #11
    Forum Contributor
    Join Date
    10-24-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    302

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks

    Here is my code btw:

    Sub t()
    Dim wb As Workbook, sh As Worksheet, fName As String
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Sheets
    fName = sh.Name & "_Model.xlsm"
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & fName)
    On Error Resume Next
    wb.Sheets("sh.Name").Delete
    On Error GoTo 0
    Err.Clear
    sh.Copy Before:=wb.Sheets(1)
    ActiveSheet.Name = sh.Name
    wb.Close True
    Next
    Application.ScreenUpdating = True
    End Sub

  12. #12
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks

    I erroneously used quotation marks on this in my first response.

    Please Login or Register  to view this content.
    Remove those quote marks and it will delete the old sheet before adding the new one. You should also probably add a couple of lines to handle alerts. It would then look like this.
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    10-24-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    302

    Re: Copy Numerous Sheets to Numerous Unopened Workbooks

    This works very well, though it's not lightning-fast. I realize it has to look through many files.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy cell A3 and Cell A5 of numerous sheets into a single sheet
    By aarona in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2017, 01:56 AM
  2. Difficulty Creating Data Connection between numerous Workbooks
    By PNommay in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-09-2012, 05:08 PM
  3. make numerous workbooks open in excel
    By Tom123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-11-2011, 02:22 PM
  4. Data Extraction from numerous workbooks
    By bholabhala in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-02-2011, 03:05 PM
  5. Retrieve data from numerous workbooks inside a Master File
    By Staci in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 03-29-2011, 07:30 PM
  6. Pull out specific values from numerous workbooks
    By jimmy1981 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-21-2010, 04:19 AM
  7. [SOLVED] Efficient way to copy a range in numerous sheets within a workbook
    By Steve O in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-14-2005, 09:05 PM

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