Merging tables from various worksheets into one larger worksheet

[ninja1980]

Hi,

I am trying to merge data from various worksheets (all the same format) that only feature the headers and one line of data into a much larger worksheet featuring all the data from the other smaller worksheets with just the one header - minus the all headers. I having a problem making the VBA script work (see below). What am I doing wrong? Any pointers would be greatly appreciated.

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
sh.Range("A1:F1").Copy DestSh.Range("A1")
End If

Sub CopyDataWithoutHeaders()
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 "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

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

'Fill in the start row
StartRow = 2

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then

'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 values/formats
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

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

 

[SkipVought]

Hi,

Please use the CODE TGML Tag when you post code. If you don't know what that is, do a FIND on TGML on this web page.

Here is some code I have used.

Sub JoinLists()
    Dim ws As Worksheet, lRowFrom As Long, lRowThru As Long, iCol As Integer
    
    iCol = ColCount
    If iCol = 0 Then Exit Sub
    
    wsJoin.UsedRange.ClearContents
    
    For Each ws In Worksheets
        If ws.Name <> [b]"YourConsolidatedDataSheetName"[/b] Then
            With wsJoin
                lRowThru = .[A1].CurrentRegion.Rows.Count + 1
                ws.UsedRange.Copy
                .Cells(lRowThru, "A").PasteSpecial xlPasteValues
                lRowFrom = lRowThru
                lRowThru = .[A1].CurrentRegion.Rows.Count
                Range(.Cells(lRowFrom, iCol), .Cells(lRowThru, iCol)).Value = ws.Name
            End With
        End If
    Next
End Sub
Function ColCount() As Integer
    Dim ws As Worksheet, iCol As Integer
    
    For Each ws In Worksheets
        If ws.Name <> wsJoin.Name Then
            If iCol = 0 Then
                iCol = ws.UsedRange.Columns.Count
                ColCount = iCol
            Else
                iCol = ws.UsedRange.Columns.Count
                If ColCount <> iCol Then
                    ColCount = 0
                    Exit Function
                End If
            End If
        End If
    Next
End Function

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]