Combining Data From Multiple Worksheets into Master

[exceltracker02]

I am using Excel 2007 and trying to combine data from regional worksheets into a master worksheet. I've found several VBA options, but none seem to work for my specific situation. I'm also open to a non-VBA solution if one exists. What I'd like to happen is for the data on the worksheets 1, 2, and 3 to compile on a 4th worksheet. The worksheets will have different numbers of rows, and the number of rows will change over time. I've tried creating dynamic named ranges using the formula:

=OFFSET('Branch 1'!$A$28,0,0,MATCH(1E+306,'Branch 1'!$A:$A,1),11)

The range seems to function, but I still can't figure out how to compile the various ranges into a single sheet. Thanks in advance for any suggestions. Also, I have an example worksheet that I'm happy to post if helpful, but I don't see a way to upload attachments. Is that possible?

 

[VoG]

Try this http://msdn.microsoft.com/en-us/library/cc793964.aspx

 

[exceltracker02]

Thanks so much for that link. I used the first block of code, and unlike previous macros I've tried this one is not returning an error. But it's still not copying properly. The RDBMerge worksheet shows up as blank, with the exception of the sheet names, which I moved from their original location (Column H) to Column O. I think I don't have the CopyRng set properly. The data on each worksheet begins in cell A28. It all has a fixed number of columns (through column N) but variable numbers of rows. Also, my headers are merged cells A23:A27, which I can easily change if that adds complication.

 

[KoE]

exceltracker02,

this is the code that i'm running...courtesy from nice people here it uses array to populate any number of worksheets that you want into 1.

1st it looks for a worksheet (this is my summarized worksheet); if found it'll delete this worksheet (as i want to populate/summarize most recent data); then it creates a new blank worksheet with similar name. Lastly it start copying all the data from all the w/sheets specified in the array.

do modify as deem fit.

Option Explicit

Sub CopyDataWithoutHeaders()
'
' Macro to summarise ALL worksheets
'
' Keyboard Shortcut: Ctrl+q
'
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete the sheet "DPPConsolidatedList" if it exists
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("DPPConsolidatedList").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "DPPConsolidatedList"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "DPPConsolidatedList"

    're-add header to the worksheet
    'fill in the 1st row with labels
    DestSh.Range("A1:AE1").Value = Array("Nama", "No. K/P", "Jawatan", "Gred", "Bertugas Di", "Berpejabat", "Negeri", "No. H/P", "No. tel. talian terus yg berdekatan", "Tarikh Lantik", "Tamat Tempoh Kontrak", "Negeri Asal", "Alamat Tetap (Daerah)", "Alamat Tetap (Negeri)", "Penempatan Terdahulu", "Tarikh Pertukaran", "Arahan Pertukaran Bil", "Jantina", "Bangsa", "Taraf Perkahwinan", "Nota", "Penempatan Pertama", "Tarikh", "Penempatan Kedua", "Tarikh", "Penempatan Ketiga", "Tarikh", "Penempatan Keempat", "Tarikh", "Penempatan Kelima", "Tarikh")
    
                                 
    'Fill in the start row
    StartRow = 2

    'loop through specified worksheets in the array and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Sheets(Array("johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", "perak", "perlis", "ibu pejabat", "wp kl", "SPRM", "Imigresen", "Kastam", "AADK", "DoF", "KPDNKK", "KDN", "APMM", "JAS", "Suruhanjaya Multimedia", "Perhutanan", "Kumpulan"))
        
            'Find the last row with data on the DestSh and sh
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            'If sh is not empty and if the last row >= StartRow copy the CopyRng
            If shLast > 0 And shLast >= StartRow Then

                'Set the range that you want to copy
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

                'Test if there enough rows in the DestSh to copy all the data
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                    MsgBox "There are not enough rows in the Destsh"
                    GoTo ExitTheSub
                End If

                'This copies everything
                CopyRng.Copy DestSh.Cells(Last + 1, "A")

            End If

     Next

ExitTheSub:

    Application.GoTo DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
        
End Sub

 

[exceltracker02]

Still struggling with this after a more detailed review. I can't figure out why my RDBSummary sheet is blank after running the macro. Any thoughts on what might cause that?

 

[exceltracker02]

Ok, finally some progress. What confused me initially (and obviously I'm not VBA literate) was this line of code:

' Specify the range to place the data.
Set CopyRng = sh.Range("A1:G1")

That actually isn't the range to place the data but the range to copy data from. So I made some adjustments and now my RDBMerge sheet is no longer blank. But I'm still having some problems. For some reason now, the macro is only copying 3 of my 10 worksheets. It copies the first two and the last one, but skips the 7 in the middle. Can't figure out what would cause this.