copy sheets to one sheet

[Computerised10]

hi

i could have up to 50 sheets all contain data. i need to copy all those sheets and data to to sheet1.

any help will be appreciated.

 

[WarPigl3t]

Sub copyAllSheetsToMasterSheet()
    masterSheet = "Master"  'The sheet name of your master sheet
    firstRow = 2  'first row of data begins in row 2 if you have headers on each sheet
    For Each wkst in Worksheets
        If wkst.name <> masterSheet Then
            lastRow = Sheets(wkst.name).Range("A" & Rows.Count).End(xlUp).Row
            lastColumn = Sheets(wkst.name).Cells(firstRow, Columns.Count).End(xlToLeft).Column
            Sheets(wkst.name).Range(Cells(firstRow, 1), Cells(lastRow, lastColumn)).Copy
            nextRow = Sheets(masterSheet).Range("A" & Rows.Count).End(xlUp).Row + 1
            [COLOR=#000080]ActiveSheet.Paste Destination:= Sheets(masterSheet).Range("A" & nextRow)[/COLOR]
            CutCopyMode = FALSE
        End If
    Next wkst
End Sub

OR

Sub copyAllSheetsToMasterSheet()
    masterSheet = "Master"  'The sheet name of your master sheet
    firstRow = 2  'first row of data begins in row 2 if you have headers on each sheet
    For Each wkst in Worksheets
        If wkst.name <> masterSheet Then
            lastRow = Sheets(wkst.name).Range("A" & Rows.Count).End(xlUp).Row
            lastColumn = Sheets(wkst.name).Cells(firstRow, Columns.Count).End(xlToLeft).Column
            Sheets(wkst.name).Range(Cells(firstRow, 1), Cells(lastRow, lastColumn)).Copy
            nextRow = Sheets(masterSheet).Range("A" & Rows.Count).End(xlUp).Row + 1
            [COLOR=#000080]Sheets(masterSheet).Range("A" & nextRow).PasteSpecial xlPasteValues[/COLOR]
            CutCopyMode = FALSE
        End If
    Next wkst
End Sub

 

[Michael M]

MAybe this

Sub ConsolidateSheets()
Dim ws As Worksheet
Dim LR As Long, NR As Long
Application.ScreenUpdating = False
NR = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For Each ws In Worksheets
    If ws.Name <> "Sheet1" Then
        LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Rows("1:" & LR).Copy Destination:=Sheets("Sheet1").Range("A" & NR + 1)
            NR = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
        End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[Computerised10]

Hey Micheal

that was perfect, thanks heaps

 

[Michael M]

glad to help...