Macro to Copy/Paste to the same sheet using a command button

[OPCuser18]

Hello, I'm in need of some help. This script I'm using is not working and keep getting errors. Been working on this for days.

I'm trying to copy a row of data (B7:G7) and send this data to another row (B35:G35) in the same sheet. If (B35:G35) is already filled, I need it to move down to the next available row with no data in it so to (B36:G36) or (B37:G37) so and and so fourth.

I'm using a command button to move the data.

Thx for the assistance!!!

Private Sub CommandButton2_Click()
'Controller and Spares paste

Dim V_Product_Guide As Worksheet

Sheets("V_Product_Guide").Select

If Range("B35:G35") <> "" Then

Range("B7:G7").copy


Range("B35").End(xlDown).Select


r = Selection.Row
Cells(r + 1, 1).EntireRow.Insert


Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste: =x1PasteValues

Else

End If


End Sub

 

[rlv01]

Perhaps something like this.

Private Sub CommandButton2_Click()
'Controller and Spares paste
    Dim rngColData As Range

    Sheets("V_Product_Guide").Select

    With Sheets("V_Product_Guide")
        If .Range("B35") <> "" Then
            Set rngColData = .Range("B" & .Rows.Count).End(xlUp).Offset(1)
        Else
            Set rngColData = .Range("B35")
        End If
  
        .Range("B7:G7").Copy
        rngColData.PasteSpecial Paste:=xlPasteValues
    End With
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.)

 

[OPCuser18]

Perhaps something like this.

Private Sub CommandButton2_Click()
'Controller and Spares paste
    Dim rngColData As Range

    Sheets("V_Product_Guide").Select

    With Sheets("V_Product_Guide")
        If .Range("B35") <> "" Then
            Set rngColData = .Range("B" & .Rows.Count).End(xlUp).Offset(1)
        Else
            Set rngColData = .Range("B35")
        End If
 
        .Range("B7:G7").Copy
        rngColData.PasteSpecial Paste:=xlPasteValues
    End With
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.)

Thanks rlv01. I will look at how to post your code doc you supplied.

As for the code. The first part works but when I go to hit the comman button again, it places the data all the way at the very bottom of my sheet on cell B231. Any way to fix. Once B35 is used up, it should place on B36 then B37.

Thanks again!

B

 

[rlv01]

it should place on B36 then B37.

Until when? What happens if you keep clicking and it eventually reaches B231?

 

[OPCuser18]

Until when? What happens if you keep clicking and it eventually reaches B231?

It continues to place them at the very bottom of the sheet if I continue to hit the Command button. Any suggestions?

 

[rlv01]

it places the data all the way at the very bottom of my sheet on cell B231.

An excel worksheet has 1,048,576 rows so when you indicate that the "very bottom" of your worksheet is row
231, I'm getting the impression that there are things about your worksheet that you are not explaining.

Try this.

Private Sub CommandButton2_Click()
'Controller and Spares paste
    Dim rngColData As Range

    Sheets("V_Product_Guide").Select

    With Sheets("V_Product_Guide")
        If .Range("B35") = "" Then
               Set rngColData = .Range("B35")
        ElseIf .Range("B36") = "" Then
               Set rngColData = .Range("B36")
        Else
            Set rngColData = .Range("B35").End(xlDown).Offset(1)
        End If

        .Range("B7:G7").Copy
        rngColData.PasteSpecial Paste:=xlPasteValues
    End With
End Sub

 

[OPCuser18]

An excel worksheet has 1,048,576 rows so when you indicate that the "very bottom" of your worksheet is row
231, I'm getting the impression that there are things about your worksheet that you are not explaining.

Try this.

Private Sub CommandButton2_Click()
'Controller and Spares paste
    Dim rngColData As Range

    Sheets("V_Product_Guide").Select

    With Sheets("V_Product_Guide")
        If .Range("B35") = "" Then
               Set rngColData = .Range("B35")
        ElseIf .Range("B36") = "" Then
               Set rngColData = .Range("B36")
        Else
            Set rngColData = .Range("B35").End(xlDown).Offset(1)
        End If

        .Range("B7:G7").Copy
        rngColData.PasteSpecial Paste:=xlPasteValues
    End With
End Sub

YES!! It worked. Thanks so much for your help.