CODEWITHSUNDEEP
excelvba
Starbucks
Starbucks

Reputation: 135

Copy and pasting a cell for the next two cells down a column

I currently have a working macro that copies and pastes a cell for the next two rows. However, I need this to work If I have multiple rows of data inputted. I can't figure out the correct code. I also want to add "0", "1", and "2" to the end of each iteration. See below for what I ideally would like. 

facctnum    facctsname  facctlname  macctnum     
    JW30982250
JW30982251
JW30982252              

Sub CopyPaste()
Dim cell As Range
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Range("B2:" & "B" & Lastrow)
If cell.Value <> "" Then Range("B2:" & "B" & Lastrow).Copy
Range("B3:B4").PasteSpecial xlPasteValues
Next
End Sub

For every cell in column B of my spreadsheet I want to paste it twice below itself and then add a "0" to the end of the first cell, "1" to the end of the 2nd cell repetition and a "3" next to the 3rd repetition.

Upvotes: 0

Views: 91

Answers (1)

BigBen
BigBen

Reputation: 50008

Something like this, looping from the bottom up and inserting an additional two cells with each iteration.

Sub CopyPaste()
    Dim lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row

    Dim i As Long
    For i = lastRow To 2 Step -1
        Range("B" & i).Offset(1).Resize(2).Insert Shift:=xlDown
        Range("B" & i).Offset(2).Value = Range("B" & i).Value & "2"
        Range("B" & i).Offset(1).Value = Range("B" & i).Value & "1"
        Range("B" & i).Value = Range("B" & i).Value & "0"
    Next i
End Sub

Upvotes: 1

Related Questions