VBA - Copy/Paste to next empty cell

  • Follow


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).
0
Reply Utf 11/20/2009 6:13:02 PM

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
-- 
Data Hog


"megangomez" wrote:

> 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).
0
Reply Utf 11/22/2009 5:18:01 PM


1 Replies
1713 Views

(page loaded in 1.288 seconds)


Reply: