Copying data based on ws name

[struf]

I need to copy data from one wb with multiple ws to another wb based on the name of the ws

First wb has 50 ws, one for each state. EX: C:\ qtr1.xlsx

In another folder, i have 50 wb , again one for each state (EX: C:\2011\ AL.xlsx), which happens to have 4 ws

I would like to copy the data on each individual in C:\ qtr1.xlsx to the appropriate wb in

C:\2011, and to make it more interesting, I need it copied to the second tab , let's call it "supplies". The Supplies tab is the same in each Wb

Now because I do not want to have to open each wb, i have found several macros that will open and close these wb once i find another macro to do all the copying. So i am not worrying about that at this time

I have tried looking at lookups, indirects, etc, but to tell you the truth, i don't even know where to start anymore.

While an answer to my problem would be fantastic, I would be very happy to get any sort of guidence so i can continue to research it.

Many thanks in advance. ANy direction is extremely appriciated.

Regards,

struf

 

[Norie]

Where do you want to copy the data from each worksheet to on the supplies worksheet of the individual state workbook?

Option Explicit
 
Sub test()
Dim wbSrc As Workbook
Dim wbDst As Workbook
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim strFileName As String
Dim strPath As String
 
   Set wbSrc = ThisWorkbook    ' assumes code is in qrt1.xlsx
 
    strPath = "C:\2011\"
 
    For Each wsSrc In wbSrc.Worksheets
 
        strFileName = wsSrc.Name & ".xlsx"    ' assumes worksheet names in qtr1.xlsx match names of individual worksheets
 
        Set wbDst = Workbooks.Open(strPath & strFileName)
 
        Set wsDst = wbDst.Worksheets("supplies")
 
        ' very simple copy for testing
        wsSrc.Range("A1").CurrentRegion.Copy wsDst.Range("A" & Rows.Count).End(xlUp).Offset(1)
 
    Next wsSrc
 
End Sub

This works, but as you can see the copy is pretty simple.

It just copies the current region of each sheet below any data in the 'supplies' worksheet.

 

[struf]

It can copy the entire sheet to the "supplies" tab. I have headers in both, but they are the same. I will play and see how she does.

Many thanks for you reply.. I love this board!

 

[Norie]

So could you just copy the entire sheet into the relevant workbook, delete the existing supplies worksheet and rename the copied worksheet?

 

[struf]

Yes, good idea, that can be done. I don't see any problems doing it that way.

 

[Norie]

Well this seems to work it goes in place of the code in the loop.

        strFileName = wsSrc.Name & ".xlsx"    ' assumes worksheet names in qtr1.xlsx match names of individual worksheets

        Set wbDst = Workbooks.Open(strPath & strFileName)

        Set wsDst = wbDst.Worksheets("supplies")
        
        Application.DisplayAlerts = False
        
        wsDst.Delete
        
        Application.DisplayAlerts = False
               
        wsSrc.Copy Before:=wbDst.Worksheets(1)
        
        wbDst.Worksheets(1).Name = "supplies"

 

[struf]

will give it a shot, thanks again

 

[struf]

Two things I noticed.

1) i need the copied sheet from C:\qtr1.xlsx to go to the second tab.
and

2) every now and then there will be a state that does not ahve anything in C:\qtr1.xlsx.
Can we pass over that without stopping the macro?

 

[Norie]

1 In the code that copies the worksheet change Before to After.

Then in the next line, which renames the copied worksheet, change 1 to 2.

2 Why would that stop the macro?

It goes through each worksheet in qtr1.xlsx, so if there are only 10 state worksheets in that workbook it will only open, copy etc the corresponding 10 workbooks.

I actually checked that by only having 10 worksheets in 10 qtr1.xlsx but a workbook for each state in C:\2011.

Or do you mean something else? eg for some states the worksheet will have no data so you don't want to copy it.

 

[struf]

Its not your code, its my data that needs tweaking. Everyhting is working perfectly.
Thank you for your time and patience.