• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA - Multiple Copy & Paste if Cell is blank

Hi All,

I have done a macro file that in sheet2 i have 7 digits that should repeat copy and paste in sheet1 at column B with refer the last row of column A, Pls help on same.
i have enclosed sample file for reference also.

Code:
Sub copy_Paste()
Dim y As Workbook
Dim lr1, lr2, lr3 As Long
Dim ws As Worksheet
Set y = ThisWorkbook
y.Activate

lr2 = .Range("A" & .Rows.Count).End(xlUp).Row
    Sheets("Sheet2").Select
    Range("A2:A8").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B5:B" & .Rows.Count).Select
    ActiveSheet.Paste
End Sub
 

Attachments

Marc L

Excel Ninja
Hi, as a reminder a dot alone before any object / property has no sense when not inside a With block …​
According to your attachment a VBA demonstration for starters :​
Code:
Sub Demo1()
        Dim R&
    With [Sheet1!A1].CurrentRegion
        For R = 5 To .Rows.Count Step 7
            [Sheet2!A2:A8].Copy .Cells(R, 2)
        Next
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

Marc L

Excel Ninja
If you do not find anything obvious in VBA help - as all is there ! - then just see this variable​
in debug step by step mode within the VBE Locals window (Ctrl + G) …​
Question : using the same method what is the data type of the lr2 variable of your VBA procedure ?​
 
Top