consolidate info multiple sheets in workbook

[TDenison]

I am fairly new to excel, how do I consolidate information from several worksheets into one?

 

[Dougie1]

The VBA below will collate multiple sheets (of the same data type and format)
onto one master sheet

Alt + F11 will take you to the code module within a workbook. Copy the code from here and paste it into the VB module of your workbook with multiple sheets (Alt + F11, then Insert Module will open a new module for you in your multiple worksheet workbook)

Ensure you are in cell A1 of the rightmost worksheet

Now run the macro in your multiple worksheet workbook and this will create one master worksheet named Master Sheet on the first sheet (leftmost) of the workbook


Sub CombineSheets()
Dim J As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Master Sheet"

' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")

' work through sheets
For J = 2 To Sheets.Count ' from second sheet to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Sheets("Master Sheet").Select
Range("A1").Select
Next
End Sub


Hope this helps

D