asked on

How to use EXCEL VBA to paste a single value down a predetermined number of rows?

I have a part number with N steps in its routing. I want to copy the part number from cell A1 in spreadsheet "Sheet1" and paste the part number in column A of another spreadsheet "Sheet 2" on N rows. I have N in a variable called NrOpns. Also, when I go to "Sheet 2" I want to find the first non-blank row in column A and start the paste operation there.

Michael Fowler

Could you post an example workbook so we have an example to work with.

Here is a VBA example based on my understanding of the explaination

Sub test()

    Dim lastUsedRow As Long, partCounter As Long, n As Long
    Dim partPrefix As String, firstPartNo As String
    
    lastUsedRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    If lastUsedRow = 1 Then lastUsedRow = 0
    firstPartNo = Worksheets("Sheet1").Range("A1")
    partPrefix = Left(firstPartNo, Len(firstPartNo) - 2)
    partCounter = Right(firstPartNo, 2)
    n = Worksheets("Sheet1").Range("B1")
    
    For i = lastUsedRow + 1 To n
       Worksheets("Sheet2").Range("A" & i).Value = partPrefix & partCounter
       partCounter = partCounter + 1
    Next
    
End Sub

Open in new window

It assumes that the last two digits are the counter and that NrOpns is in cell B1. I have also attached a workbook with this Macro
Example.xlsm

EdLB

ASKER

Attached is a file with an example of the two sheets and the data.
Copy-and-Paste-Example.xlsx

ASKER CERTIFIED SOLUTION

Michael Fowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EdLB

ASKER

Thanks Michael, that's a huge help. And thanks also for getting back to me so quickly.