Copy Numerous Sheets to Numerous Unopened Workbooks

[hchavous]

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.

[JLGWhiz]

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.

[hchavous]

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.

[JLGWhiz]

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.

[Marc L]

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 …

[hchavous]

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

[Marc L]

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) …

[hchavous]

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

[Marc L]

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 …

[hchavous]

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?

[hchavous]

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

[JLGWhiz]

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.

[hchavous]

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