Macro to copy and paste from different sheets to 1 single sheet

mansoormanzoor

New Member
Joined
Jun 3, 2008
Messages
33
Hi,

I have a workbook with that 950+ worksheets, need to combine them in 1 single spreadsheet. My requirement is to copy and paste the formats and numbers only as each sheet has calculated values. Also I would like to add 3 new lines every time after pasting the data.The code I am using pastes everything. Here is the code:

Sub combine()




Dim Sht As Worksheet
Sheets("Master").Select
Range("A2").Select
Range("A2:H2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents


For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" And Sht.Range("A2").Value = "" Then
Sht.Select
LastRow = Range("A65536").End(xlUp).Row
Range("A2", Cells(LastRow, "M")).Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Else


End If


Next Sht


Application.CutCopyMode = False
Sheets("Master").Select
Range("A1").Select




End Sub

Thanks!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
try this on a copy of you file

Code:
Sub combine()
Application.ScreenUpdating = False

Dim Sht As Worksheet
Range("A2", [A2].SpecialCells(xlLastCell)).ClearContents


For Each Sht In ActiveWorkbook.Worksheets

Sht.Activate
If Sht.Name <> "Master" And Sht.Range("A2").Value <> "" Then
LastRow = Range("A65536").End(xlUp).Row
Range("A2", Cells(LastRow, "M")).Copy

Sheets("Master").Select
Range("A65536").End(xlUp).Offset(4, 0).Select
ActiveSheet.Paste
End If
Next Sht


Application.CutCopyMode = False
Range("A1").Select


Application.ScreenUpdating = True

End Sub

hth,

Ross
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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
Back
Top