Hi,
I have a workbook with 7 tabs, 1 called "Main" and 1 called "Reference". Others have other names (Let's call them Sheet1, Sheet2 etc.).
I am trying to copy the data held in Sheet1, Sheet2 etc. into the "Main" tab, one under each other, so as to create a masterlist of data held in the other tabs. I want it to copy all data below row 7 from the source sheets into the Main sheet (which starts at row 7 too). Each number of rows in the source lists varies and I want it to paste over/delete what was in the Main sheet before.
This is what I have so far, but I'm not sure it's useful...
Sub SummariseSheets()
Dim ws As Worksheet
Application.ScreenUpdating = False
Sheets("Main").Activate
For Each wsSheet In Worksheets
If wsSheet.Name <> "Main" Or _
wsSheet.Name <> "References" Then
ws.Range("B7:N29").Copy
Worksheets("Main").Range("B6").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)
End If
Next ws
End Sub
Any help would be really appreciated.
I have a workbook with 7 tabs, 1 called "Main" and 1 called "Reference". Others have other names (Let's call them Sheet1, Sheet2 etc.).
I am trying to copy the data held in Sheet1, Sheet2 etc. into the "Main" tab, one under each other, so as to create a masterlist of data held in the other tabs. I want it to copy all data below row 7 from the source sheets into the Main sheet (which starts at row 7 too). Each number of rows in the source lists varies and I want it to paste over/delete what was in the Main sheet before.
This is what I have so far, but I'm not sure it's useful...
Sub SummariseSheets()
Dim ws As Worksheet
Application.ScreenUpdating = False
Sheets("Main").Activate
For Each wsSheet In Worksheets
If wsSheet.Name <> "Main" Or _
wsSheet.Name <> "References" Then
ws.Range("B7:N29").Copy
Worksheets("Main").Range("B6").End(xlDown).Offset(1, 0).PasteSpecial (xlPasteValues)
End If
Next ws
End Sub
Any help would be really appreciated.