Merging data from several different spreadsheets

**Lahuka** · 08-08-2009, 10:54 AM

Hi all, this is my first post and was wondering if anyone could help me out with my limited Excel VBA knowledge?

Basically I have the attached spreadsheet several times with different info on each. If you look at the NonSap tab you will notice that data is present for the Elec Acquisitions process. Each different spreadsheet will have data for 1 or 2 of the processes.

I have got some code that will loop through and get the names of all the spreadsheets, and open them one after the other, and I need some code that will identify which cells have been populated, and then copy them over to a master spreadsheet.

If anyone can help I will be very grateful as I have been trying to get my head around how to do this for a while now!

Cheers

Luke

**JBeaucaire** · 08-08-2009, 11:54 AM

Your code works very well so far. You know where the top of the data range is, always starts at row11. If you add a variable to spot the bottom of the data on each worksheet, it makes copying to another sheet very simple. Here's a macro:

Sub ConsolidatedReport()
'Copy selected data range from all sheets except sheets named MAIN,
'REPORT and SCORECARD   -  JBeaucaire (8/8/2009)
Dim ws As Worksheet, LR As Long

'Erase old report
    Sheets("Report").Range("B11:K" & Rows.Count).Clear

'Consolidate new report
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Main", "Report", "Scorecard"
                'do nothing
            Case Else
                ws.Activate
                Range("B10").AutoFilter Field:=1
                LR = Range("B" & Rows.Count).End(xlUp).Row
                Range("B10").AutoFilter Field:=1, Criteria1:=Range("C1").Value
                Range("B11:K" & LR).SpecialCells(xlCellTypeVisible).Copy _
                    Sheets("Report").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
        End Select
    Next ws
    
Sheets("Report").Activate
End Sub

I've installed in your sheet and added a new button to run it. Pick a Process then click the new button.