excel vba paste values only Option Explicit Sub Copy2ndRow() Dim ws As Worksheet

Question

excel vba paste values only

Option Explicit
Sub Copy2ndRow()
Dim ws As Worksheet, nr As Long
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "MTD" Then
nr = Sheets("MTD").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Rows(2).Copy Sheets("MTD").Rows(nr)
End If
Next ws
End Sub

Hello -
My goal is to copy A2:P2 from 31 daily sheets to MTD sheet and sum all columns.... the code above does everything I need except formatting - I would like to paste values only but keep getting an error when I try to add the code below

.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

also would like to have this run whenever the workbook is closed

Thank you in advance!!

Explanation / Answer

Hi..

Option Explicit
Sub Copy2ndRow()
Dim ws As Worksheet, nr As Long
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "MTD" Then
nr = Sheets("MTD").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Rows(2).Copy

Sheets("MTD").Cells( nr, "A").PasteSpecial xlValues
Application.CutCopyMode = False

End If
Next ws
End Sub

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.