VBA - Copy/Paste to next empty cell

  • Thread starter Thread starter megangomez
  • Start date Start date
M

megangomez

I would like to have a button that, when clicked, will copy the contents of a
specific row and paste it into the next empty row of several subsequent
ranges.

Example:

Range 1: B8:E12
Range 2: B20:E24
Range 3: B32:E36

There is text in B8 and a number in E8.

I would like to click a button that will paste the information in B8 and E8
to the first empty rows in Ranges 2 and 3 (could be B20/E20 of Range 1 and
B34/E34 of Range 2, and so on, depending on which row is empty in each range).
 
See if this is what you're after.

Sub CopyPasteTwoPlaces()
' Copies B8:E8 to B20:F31 and B32:F41 and beyond
Dim i As Long
Range("B8:E8").Copy
For i = 20 To 31 Step 1 '12 places for first paste values
If IsEmpty(Cells(i, 2).Value) Then Exit For
Next
Debug.Print i
Cells(i, 2).Select
ActiveSheet.Paste
If IsEmpty(Range("B32").Value) Then
Range("B32").Select
Else
Range("B" & Range("B:B").Rows.Count).End(xlUp).Offset(1, 0).Select
End If
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub


HTH
 
Back
Top