Combine all sheets into one - need help with existing code

[rachel06]

Hi,

I have a spreadsheet with multiple sheets that I need to combine into one. These sheets have reports with the exact same structure, using columns A-L and no headers. I want to copy the data into a sheet called "ALL" which does have headers, so it would need to start in row two.

Anyway, this is what I came up with but it made Excel not responsive. I'm sure there's a better way and I'd love some suggestions!

Option Explicit


Sub JobID()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> "DASHBOARD" And ws.Name <> "ALL" Then
            With ws
            Range("A1:L1").Select
            Range(Selection, Selection.End(x1Down)).Select
            Selection.Copy
            Sheets("ALL").Select
            Cells(Rows.Count, "A").End(x1Up).Offset(1).Select
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Try:

Sub JobID()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> "DASHBOARD" And ws.Name <> "ALL" Then
            With ws
                .UsedRange.Copy Sheets("ALL").Cells(Sheets("ALL").Rows.Count, "A").End(x1Up).Offset(1)
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[rlv01]

Perhaps something like this.

Sub JobID()
    Application.ScreenUpdating = False
    Dim WS As Worksheet
    Dim wsAll As Worksheet
    Dim LastRow As Long
    Dim rngSrc As Range
    Dim rngDest As Range
    
    Set wsAll = ThisWorkbook.Worksheets("ALL")
    
    For Each WS In Worksheets
        Select Case WS.Name
            Case "DASHBOARD", "ALL"
            Case Else
                With WS
                    Set rngSrc = .Range("A1:L" & .Range("A" & .Rows.Count).End(xlUp).Row)
                End With
                rngSrc.Copy
                
                With wsAll
                    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
                    Set rngDest = .Cells(LastRow + 1, 1)
                    rngSrc.Copy
                    rngDest.PasteSpecial xlPasteValues
                End With
        End Select
    Next WS
    Application.ScreenUpdating = True
End Sub

 

[rachel06]

Try:

Sub JobID()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> "DASHBOARD" And ws.Name <> "ALL" Then
            With ws
                .UsedRange.Copy Sheets("ALL").Cells(Sheets("ALL").Rows.Count, "A").End(x1Up).Offset(1)
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Appreciate the quick response!! Getting an error "Compile error: Variable not defined" This part is highlighted from this line of code ".End(x1Up)"

.UsedRange.Copy Sheets("ALL").Cells(Sheets("ALL").Rows.Count, "A").End(x1Up).Offset(1)

Any suggestions? I have it set as Option Explicit.

 

[mumps]

Could you upload a copy of your file (de-sensitized if necessary) to a free file sharing site like Dropbox.com and post a link to your file?

 

[rachel06]

Could you upload a copy of your file (de-sensitized if necessary) to a free file sharing site like Dropbox.com and post a link to your file?

Another user posted something that worked perfectly. Thanks so much for responding though - much appreciated!

 

[rachel06]

Perhaps something like this.

Sub JobID()
    Application.ScreenUpdating = False
    Dim WS As Worksheet
    Dim wsAll As Worksheet
    Dim LastRow As Long
    Dim rngSrc As Range
    Dim rngDest As Range
   
    Set wsAll = ThisWorkbook.Worksheets("ALL")
   
    For Each WS In Worksheets
        Select Case WS.Name
            Case "DASHBOARD", "ALL"
            Case Else
                With WS
                    Set rngSrc = .Range("A1:L" & .Range("A" & .Rows.Count).End(xlUp).Row)
                End With
                rngSrc.Copy
               
                With wsAll
                    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
                    Set rngDest = .Cells(LastRow + 1, 1)
                    rngSrc.Copy
                    rngDest.PasteSpecial xlPasteValues
                End With
        End Select
    Next WS
    Application.ScreenUpdating = True
End Sub

Perfect. Thank you!!!!

 

[dmt32]

Appreciate the quick response!! Getting an error "Compile error: Variable not defined" This part is highlighted from this line of code ".End(x1Up)"

Just to answer your question, the error likely to result if member inadvertently entering the number 1 when it should be l (lower case L)

.End(xlUp)

Dave