• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

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.
0
EdLB
Asked:
EdLB
  • 2
  • 2
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
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
0
 
EdLBAuthor Commented:
Attached is a file with an example of the two sheets and the data.
Copy-and-Paste-Example.xlsx
0
 
Michael FowlerSolutions ConsultantCommented:
Here you go

Sub CopyPart()
    Dim firstRow As Long, stepCount As Long
    
    firstRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
    stepCount = Worksheets("Sheet1").Range("H3").Value
    
    Worksheets("Sheet2").Range("A" & firstRow, "A" & firstRow + stepCount - 1).Value = Worksheets("Sheet1").Range("A3").Value
    
End Sub

Open in new window

Copy-and-Paste-Example.xlsm
0
 
EdLBAuthor Commented:
Thanks Michael, that's a huge help. And thanks also for getting back to me so quickly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now