Paste values code

[davez]

Hi all, I have this code which works fine for copying ranges from multiple sheets to a Master sheet. What I need to do is paste the values as some of the copied cells do contain formulas & I have played around but cannot work out where to place "PasteSpecial Paste:=xlPasteValues". Thanks for any help provided.

Sub COPY_RESULTS_TO_MASTER()
Dim ws As Worksheet, lr As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each ws In Worksheets
    If ws.Name <> "Master" Then
        ws.Range("T1:AH60").Copy Sheets("Master").Range("A" & lr)
        lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
Next ws
End Sub

 

[kevin9999]

Try this:

Sub COPY_RESULTS_TO_MASTER()
Dim ws As Worksheet, lr As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each ws In Worksheets
    If ws.Name <> "Master" Then
        ws.Range("T1:AH60").Copy
       Sheets("Master").Range("A" & lr).PasteSpecial xlPasteValues
       Application.CutCopyMode = False
        lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
Next ws
End Sub

 

[davez]

Try this:

Sub COPY_RESULTS_TO_MASTER()
Dim ws As Worksheet, lr As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each ws In Worksheets
    If ws.Name <> "Master" Then
        ws.Range("T1:AH60").Copy
       Sheets("Master").Range("A" & lr).PasteSpecial xlPasteValues
       Application.CutCopyMode = False
        lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
Next ws
End Sub

Thanks, I added a full stop before the PasteSpecial to get it running, works well.

 

[Peter_SSs]

I have added the "." to post #2 and marked that as the solution since that post was the gist of the answer & would help future readers the best.