Combining Question

[pourcho]

I have looked through the existing post and can not find exactly what I need. It maybe out there I just can't find it.

My problem:

I have 26 Excel Reports that come out everyday I save them to a folder on my Desktop and change the name to the same thing everyday "Report(1), Report(2)" etc. What I need to do is copy Sheet 4 out of each Report to One "Combine Report" I don't want to combine them on a Master sheet but in a master Workbook.

I need a quick way to do this because Now I am opening all the workbooks and Copy and Pasting that one sheet to a file called "CombineReports.xlsx"

Any help with the code would be greatly appreciated.

Thank You in Advance,
Abbye

 

[Drrellik]

when you save these 26 reports into one folder are they dated? you can take the time to create a Master Workbook where each sheet is a link from the other reports like this. ='[Report 1.xlsx]Sheet4'!A1, put this in master workbook sheet 1 then drag down an across for as much as you need. turn off all the 0's under the advanced tab if they are showing and then repeat for sheet 2 / report 2, sheet 3 / report 3.. but if any data changes or is not avail the master will not work anymore because it is just a link to he data.

 

[pourcho]

Thank you so much for your help.

I did find what I was looking for.


Sub CopySheets4()
Dim wkb As Workbook
Dim sWksName As String


For Each wkb In Workbooks
If wkb.Name <> ThisWorkbook.Name Then
wkb.Worksheets(4).Copy _
Before:=ThisWorkbook.Sheets(1)
End If
Next
Set wkb = Nothing
End Sub


It works for what I needed.

Thanks,
Abbye