Macro loop to paste as value

[Negi1984]

Hi All,

I am using below mentioned code for consolidating data.
can any one assist me to paste all the data as value while consolidating in "consolidate data" sheet.

Sub CopyRange()
        Dim bottomD As Integer
    Application.ScreenUpdating = False
    Dim WS As Worksheet
    For Each WS In Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27"))
        WS.Activate
        bottomD = Range("k" & Rows.Count).End(xlUp).Row
        Range("A2:k" & bottomD).Copy Sheets("Consolidate Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next WS
    Application.ScreenUpdating = True
End Sub

[CODE]
Thanks in advance for your valuable suggestion.

Regards,
Rajender

 

[Truiz]

[COLOR=#333333]Sub CopyRange()[/COLOR]
[COLOR=#333333]Dim bottomD As Integer[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]Dim WS As Worksheet[/COLOR]
[COLOR=#333333]For Each WS In Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27"))[/COLOR]
[COLOR=#333333]WS.Activate[/COLOR]
[COLOR=#333333]bottomD = Range("k" & Rows.Count).End(xlUp).Row[/COLOR]
[COLOR=#333333]Range("A2:k" & bottomD).Copy
 Sheets("Consolidate Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).[/COLOR][COLOR=#303336][FONT=inherit]PasteSpecial xlPasteValues[/FONT][/COLOR][COLOR=#333333]
[/COLOR][COLOR=#333333]Next WS[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[Negi1984]

[COLOR=#333333]Sub CopyRange()[/COLOR]
[COLOR=#333333]Dim bottomD As Integer[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]Dim WS As Worksheet[/COLOR]
[COLOR=#333333]For Each WS In Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27"))[/COLOR]
[COLOR=#333333]WS.Activate[/COLOR]
[COLOR=#333333]bottomD = Range("k" & Rows.Count).End(xlUp).Row[/COLOR]
[COLOR=#333333]Range("A2:k" & bottomD).Copy
 Sheets("Consolidate Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).[/COLOR][COLOR=#303336][FONT=inherit]PasteSpecial xlPasteValues[/FONT][/COLOR][COLOR=#333333]
[/COLOR][COLOR=#333333]Next WS[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

Hi Truiz,

Thanks a lot for your prompt support.
Above code working fine for me .

Regards,
Rajender