Copy all sheets into master sheet

[bsnapool]

Hi All

I tried searching but could not get code I wanted for this...

I would like to copy all sheets of data into 1 master copy.. Anyone got any code to do this?

Thanks in advance

Andrew

 

[nehpets12]

Try the following code



Sub Merge()
Dim ws As Worksheet
'Application.ScreenUpdating = False
'loop thrue all sheet
'For Each Sheet In Sheets
Sheets("Merge").Select


ActiveSheet.UsedRange.Offset(0).Clear
For Each ws In ActiveWorkbook.Worksheets
'skip active sheet
If ws.Name <> ActiveSheet.Name Then
If ws.Name = "Files" Then Exit For

'If Sheet.Name <> ActiveSheet.Name Then
'Sheet.Range("A1:C10").Copy
ws.UsedRange.Offset.Copy
'find last empty cell in column A
Range("B65536").End(xlUp).Offset(1, -1).Select
'paste the copied data

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False





'ActiveSheet.Paste
End If
Next
'Application.ScreenUpdating = True

'100
Range("a1").Select
Application.CutCopyMode = False
Cells.Select
'Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("Headings").Select
End Sub

 

[bsnapool]

Come up with the error:

Subscript out of range
[Quote/]

Runtime error 9

????

 

[nehpets12]

Add a sheet called Merge and a Sheet called headings and change to this

Range("A65536").End(xlUp).Offset(1, 0).Select

 

[bsnapool]

Perfect, Thanks very Much

Andrew

 

[UPN]

certain cells

is there a way to make a summary sheet from only specified sheets and only certain cells from those sheets

the cells will all be the same one in each sheet