Hi, I have the following code but I want to use the Selection.Copy Destination and have it Paste Values as well. At the moment the code copies OK but if I have a formula in one row then it copies the formula, any ideas how I can combine the two.
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets("Merge").Select
Worksheets("Merge").Range("A2:AB65536").Clear
' copy headings
Sheets("Calanova Golf & Sea All").Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets("Merge").Range("A1")
' work through sheets
For J = 2 To 28 ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
Worksheets("Datesheet").Select
End Sub
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets("Merge").Select
Worksheets("Merge").Range("A2:AB65536").Clear
' copy headings
Sheets("Calanova Golf & Sea All").Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets("Merge").Range("A1")
' work through sheets
For J = 2 To 28 ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
Worksheets("Datesheet").Select
End Sub