VBA to copy and paste multiple sheets

[cruyff73]

Hi,

I'm trying to consolidate info held on 128 sheets using the code below but it doesn't work. Can anyone suggest a correction?

Sub CombineSheets()
Dim rng As Range
Set rng = Sheets("master log").Range("A2:s25000")
Sheets("master log").Activate
rng.ClearContents
Dim i As Integer
For i = 1 To 128
Sheets(i).Range("a7:s2500").Copy
lastrow = Sheets("master log").Cells(Sheets("master log").Rows.Count, 1).End(xlUp).Row + 1
Sheets("master log").Cells(lastrow, 1).Paste
Application.clearcopymode
Application.DisplayAlerts = False
Next i
Range("A1").Select
End Sub

Thanks a lot

 

[Yongle]

Try

Sub CombineSheets()
    Dim ws As Worksheet, sh As Worksheet, i As Integer, lastRow As Long
    Set ws = Sheets("master log")
    ws.Range("A2:s25000").ClearContents
    For i = 1 To 128
        Set sh = Sheets(i)
        lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
        sh.Range("a7:s2500").Copy ws.Cells(lastRow, 1)
    Next i
    ws.Activate
    Range("A1").Select
End Sub

Assumes "master log" is sheet number 129 (or greater) in the same workbook

 

[Fluff]

Try making these 2 changes

Sheets("master log").Cells(lastrow, 1).[COLOR=#ff0000]PasteSpecial[/COLOR]
Application.[COLOR=#ff0000]CutCopyMode = False
[/COLOR]