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

[mansoormanzoor]

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!

 

[rpaulson]

try this on a copy of you file

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