Combining some sheets into another sheet

Hi all.

Really appreciate everyone's help on this.

I have a number of sheets in my workbook which I want to combine to a single sheet.

The Data I want to copy is from A2:M? on Sheets called F1, F2, F3, F4, and pasted into a 5th sheet called FCombined.

All of the above sheets have exactly the same layout.

I did a search on combining sheets but the results threw up solutions where the sheets were the only ones in the entire workbook, I have other sheets which do different things.

The only issue is that the quantity of records could change.

I would be enormously grateful if anyone could help me out on this.

Thanks in advance.

Simon

Try:

Sub test()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "FCombined" Then

With ws.Range("A1").CurrentRegion

.Offset(1, 0).Resize(.Rows.Count - 1, 13).Copy Sheets("FCombined").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End With

End If

Next ws

End Sub

Dom

Untested (ie coded on the fly) but perhaps:

Sub Collate()
Dim ws As Worksheet, wsFinal As Worksheet, rngCopy As Range, xlCalc As xlCalculation
On Error GoTo ExitPoint
With Application
    xlCalc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With
Set wsFinal = Sheets("FCombined")
With wsFinal
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A")).Resize(,13).Clear
End With
For Each ws In ThisWorkbook.Worksheets
    Select Case UCase(ws.Name)
        Case "F1", "F2", "F3", "F4"
            With ws
                Set rngCopy = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 13)
            End With
            With wsFinal
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count) = rngCopy.Value
            End With
    End Select
Next ws
ExitPoint:
Set wsFinal = Nothing
With Application
    .Calculation = xlCalc
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

the above assumes

a) final sheet has header row

b) final sheet should be purged with each collation

c) values only copy


EDIT: 12:14 - missing comma in the .Clear line at beginning of routine - did say it was untested

I'm sure Donkey's works but if not...

Sub test()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    Select Case ws.Name

    Case "F1", "F2", "F3", "F4"

        With ws.Range("A1").CurrentRegion

            .Offset(1, 0).Resize(.Rows.Count - 1, 13).Copy _
                Sheets("FCombined").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

        End With

    End Select

End If

Next ws

End Sub

Dom

Originally Posted by Simon

Donkey...

Note my edit - I'd omitted a comma in the Clear which will if left unchanged cause problems on re-run (will not purge correctly).