Copy and Paste

[winon]

I found this Code in this Forum, and I was hoping that someone could help me change it to paste Values.




Sub CopySheets()


' stanleydgrom, 12/02/2009


Dim MySheets As Variant, LR As Long, NR As Long, a As Long


MySheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")


Application.ScreenUpdating = False


NR = 2


For a = LBound(MySheets) To UBound(MySheets)


With Sheets(MySheets(a))


LR = .Cells(Rows.Count, "V").End(xlUp).Row


.Range("C10:V" & LR).Copy Sheets("Summary").Range("A" & NR)


End With


NR = NR + LR - 10 + 1


Next a


Sheets("Summary").Select


Application.ScreenUpdating = True


End Sub




Thank you in advance.

[Bob Phillips]

Sub CopySheets()
' stanleydgrom, 12/02/2009
Dim MySheets As Variant, LR As Long, NR As Long, a As Long

MySheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")

Application.ScreenUpdating = False

NR = 2

For a = LBound(MySheets) To UBound(MySheets)

With Sheets(MySheets(a))

LR = .Cells(Rows.Count, "V").End(xlUp).Row
With .Range("C10:V" & LR)

.Copy Sheets("Summary").Range("A" & NR)
.Value = .Value
End With
End With

NR = NR + LR - 10 + 1
Next a

Sheets("Summary").Select

Application.ScreenUpdating = True
End Sub

[winon]

Thank you xld,

Your Code copies the Formula to the destination sheet and not the value.:crying:

[mana]

Sub CopySheets()
' stanleydgrom, 12/02/2009
Dim MySheets As Variant, LR As Long, NR As Long, a As Long

MySheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")
Application.ScreenUpdating = False
NR = 2
For a = LBound(MySheets) To UBound(MySheets)
With Sheets(MySheets(a))
LR = .Cells(Rows.Count, "V").End(xlUp).Row
.Range("C10:V" & LR).Copy
Sheets("Summary").Range("A" & NR).pastespecual xlPasteValues
End With
NR = NR + LR - 10 + 1
Next a
Application.DataEntryMode = False

Sheets("Summary").Select
Application.ScreenUpdating = True

End Sub

[snb]

Sub M_snb()
for each it in sheets
if it.name <>"Summary" then sheets("summary").cells(rows.count,1).end(xlup).offset(1).resize(it.usedrange.rows.count,it .usedrange.columns.count)=it.usedrange.value
next
End Sub