vba Loop copy paste macro across 31 sheets

[Deliverable7]

Hi

I have a spreadsheet with 31 pages each containing daily input data and a messy range of calculations. I have modified the current spreadsheet to include complex data ranges (on a new tab) from which i pull up some graphs.

I now need to use this current spreadsheet as a template and to import data from previous months, each which is stored in separate spreadsheets. I have created a simple macro that can copy the input fields from one open tab on the new month file and paste into the corresponding tab on the template file.

So in summary I currently operate by:
'June.xls tab '1' open, Template.xls tab '1' open - execute the macro which copies the data ranges from June '1' to Template '1'.
I then open the respective tab '2's on each file and run the macro again, then for 3, then for 4 and so on.

I need to do this 31 times for each month (and probably need to convert 12 months of data).

Is there a way to run the macro once looping through until the input data from June '1' through to June '31' is copied across to the corresponding tabs on the Template file?

Thanks in anticipation.

 

[tonyyy]

Deliverable7,

You might consider...

Sub AnotherLoop()
Dim wb As Workbook, wbSource As Workbook
Dim ws As Worksheet, wsSource As Worksheet
Dim fNameAndPath As Variant
Set wb = ThisWorkbook

fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS?), *.XLS?", Title:="Select File To Open")
If fNameAndPath = False Then Exit Sub
Set wbSource = Workbooks.Open(Filename:=fNameAndPath)

For Each ws In wb.Worksheets
    For Each wsSource In wbSource.Sheets
        If ws.Name = wsSource.Name Then
            '''' Place your copy/paste code here
            '''' For example: wsSource.Range("A1:G20").Copy Destination:=ws.Range("A1")
            Exit For
        End If
    Next wsSource
Next ws
End Sub

The code will open a file dialog box and prompt you to choose a file/workbook, then will proceed to copy/paste data from/to matching worksheets.

Cheers,

tonyyy

 

[Deliverable7]

Hi tonyyy
Thanks for your feedback.
Can i restrict the range of sheets to copy/past to say '1', '2','3' and so on until '31'.
Thanks
Colin

 

[tonyyy]

Aren't the sheets in the template workbook named 1, 2, 3, etc?

 

[Deliverable7]

Yes both workbooks contain the 1 to 31 sheets plus others. I can control the number of additional sheets in the template file however as the June file is controlled by others they can add (or remove) some of these additional sheets. Apologies for omitting this in the initial request.

 

[tonyyy]

Change this line...

If ws.Name = wsSource.Name Then

to...

If ws.Name = wsSource.Name And IsNumeric(CLng(ws.Name)) Then

Assuming your other sheets have text names and not pure number names then this will exclude them from being copied.

 

[tonyyy]

And we can actually eliminate the CLng from the line...

If ws.Name = wsSource.Name And IsNumeric(ws.Name) Then

 

[Deliverable7]

Hi tonyyy
That's exactly what I'm after.

Thank you so much. The macro is going to be very useful.

Just for future reference how would the code need to be modified to cater for non-numeric sheet names i.e. if I needed to copy paste between matching sheets say 'Week 1', 'Week 2', 'Week 3' and so on. I'm sure to come across other variations on the sheet names.

Regards

 

[tonyyy]

Thank you so much. The macro is going to be very useful.

You're very welcome.

...how would the code need to be modified to cater for non-numeric sheet names...

Just revert back to the original code...

If ws.Name = wsSource.Name Then