Change this code to paste as values only

[thedeadzeds]

Guys, I have found this code which works great for me but i want to paste the data into the 'All data' sheet as values. I cant for the life of me figure out where to put the paste special code. I have tried several different ways but not joy. Can anyone help?

Sub CopyRange()    Dim bottomD As Integer
    Dim ws As Worksheet
    For Each ws In Sheets(Array("A", "B", "C", "D"))
        ws.Activate
        bottomD = Range("N" & Rows.Count).End(xlUp).Row
        Range("A2:N" & bottomD).Copy Sheets("All Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next ws
End Sub

 

[MARK858]

Try

Sub CopyRange()
Dim bottomD As Long
    Dim ws As Worksheet
    For Each ws In Sheets(Array("A", "B", "C", "D"))
        ws.Activate
        bottomD = Range("N" & Rows.Count).End(xlUp).Row
        Range("A2:N" & bottomD).Copy
        Sheets("All Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next ws
End Sub

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim bottomD As Long
    Dim ws As Worksheet
    For Each ws In Sheets(Array("A", "B", "C", "D"))
        bottomD = ws.Range("N" & ws.Rows.Count).End(xlUp).Row
        ws.Range("A2:N" & bottomD).Copy
        Sheets("All Data").Cells(Sheets("All Data").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[thedeadzeds]

Thank you both awesome

 

[MARK858]

You're welcome but please use the code by mumps as that code tidies the rest of the code for you.

 

[mumps]

Glad to help.