Combining Worksheets on one sheet with Paste Special

[Brooke29]

I found the below code on the internet, works great with one minor problem, I need to paste values since my origination data is all formulas. Any help would be appreciated.

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A12").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A12")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A12").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 12).Resize(Selection.Rows.Count - 20).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

 

[MattH1]

Hey Brooke and welcome to the forums. Please do try to use the CODE tags (looks like a # in the toolbar above where you type.)

Let me know if this helps.

 Sub Combine()
 Dim J As Integer
 On Error Resume Next
 Sheets(1).Select
 Worksheets.Add
 Sheets(1).Name = "Combined"
 Sheets(2).Activate
 Range("A12").EntireRow.Select
 Selection.Copy Destination:=Sheets(1).Range("A12")
 For J = 2 To Sheets.Count
 Sheets(J).Activate
 Range("A12").Select
 Selection.CurrentRegion.Select
 Selection.Offset(1, 12).Resize(Selection.Rows.Count - 20).Copy
 Sheets(1).Range("A65536").End(xlUp).Paste xlPasteValues
 Next
 End Sub

 

[Brooke29]

Perfect, thanks so much!