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!
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!