Consolidating multiple worksheets into one worksheet

[Con]

Hi,

I desperately need some help to append multiple sheets within a workbook into one master spreadsheet. I have tried the examples I have found in this forum but have not worked for me. I am very new to VBA and using macros and am using Excel 2003.

All sheets within the workbook have the same headings and format but the number of rows differ form sheet to sheet. The sheets are labeled as a series of numbers.

Any help would be greatly appreciated


Con

 

[dermie_72]

Try this: - pls take note of the prerequites because I didn't know the file names in question.

Sub CopyWorksheets()
'http://www.mrexcel.com/forum/excel-questions/670730-consolidating-multiple-worksheets-into-one-worksheet.html
'Prerequisite. - Have workbook called Master Open.  Have the copy file open and active.
Dim xlSht As Excel.Worksheet
For Each xlSht In ActiveWorkbook.Worksheets
    Rownum = xlSht.UsedRange.Rows.Count
    xlSht.Range("A2:A" & Rownum).EntireRow.Copy
    Windows("master.xlsx").Activate
    Rownum1 = ActiveSheet.UsedRange.Rows.Count
    Range("A" & Rownum1 + 1).Select
    ActiveSheet.Paste
Next xlSht
End Sub

Let me knwo how you go.