Copy all sheets into master sheet

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
453
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
 

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
453
Add a sheet called Merge and a Sheet called headings and change to this

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

UPN

Board Regular
Joined
May 14, 2006
Messages
138
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
 

Forum statistics

Threads
1,136,613
Messages
5,676,820
Members
419,653
Latest member
analyticalchemist94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top