Copy and Paste Rows VBA and loop through all sheets...

[bran8989]

Need help, here is my code.. I want it to loop through all available sheets, but on the i + 1 sheets I want it to paste to the next available empty row (i.e. I need "i" to be changing to in my range)..

Sub Paste_NextRow()


Dim wsCopy As Worksheet
Dim wsPaste As Worksheet

i = Sheets.Count


For i = 2 To Sheets.Count

Set wsCopy = Sheets(i)
Set shtInput = Sheets(1)


If i = 2 Then
shtInput.Range("C" & i).Value = wsCopy.Range("OA_CPA").Value
shtInput.Range("D" & i).Value = wsCopy.Range("OA_CPA").Value
shtInput.Range("E" & i).Value = wsCopy.Range("OA_CPA").Value
shtInput.Range("F" & i).Value = wsCopy.Range("OA_CPA").Value

End If


Next i


End Sub

 

[JackDanIce]

Welcome to the board. Not tested, save your file before running macro, however, try:

Sub Paste_NextRow()

    Dim x       As Long

    Application.ScreenUpdating = False
    
    With Sheets(1)
        For x = 2 To Worksheets.Count
            .Cells(.Cells(.Rows.Count, 3).End(xlUp).Row + 1, 3).Resize(, 4).Value = Sheets(x).Range("OA_CPA").Value
        Next x
    End With
    
    Application.ScreenUpdating = True

End Sub

 

[bran8989]

Works thanks!

 

[JackDanIce]

You’re welcome