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.

[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