Copy.Destination and Paste Special

[Rusty2712]

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

 

[Lewiy]

This should give you the result you want, and notice there is no need for sheet activation or range selection.

Worksheets("Merge").Rows(1).Value = Worksheets("Calanova Golf & Sea All").Rows(1).Value

 

[Rusty2712]

Thanks Lewiy

I think I am being thick today, where does this bit of code go?

Cheers

Russ

 

[Lewiy]

This particular bit would go in place of:

' copy headings 
Sheets("Calanova Golf & Sea All").Activate 
Range("A1").EntireRow.Select 
Selection.Copy Destination:=Sheets("Merge").Range("A1")