Using EXCEL VBA, I want to copy a row of cells of variable length and paste into a column at the first blank row in the column

In trying to modify code from previous expert help, I am trying to use the following line to copy a row of length NrOpns from "Sheet 1" and paste into "Sheet 2" in the first non-blank cell.

See attached spreadsheet for illustration.

'Copy row of value of length NrOpns from "Sheet1" into "Sheet2"
Worksheets("Sheet2").Range("C" & firstRow, "C" & firstRow + NrOpns - 1).String = Worksheets("Sheet1").Range(Cells(2, 9), Cells(2, 9 + NrOpns)).String

As is, it causes VB to choke. If I change .string to .value, it only copies the first cell (2,9) into NrOpns number of rows in Sheet2.
Copy-and-Paste-Example.xlsx
EdLBAsked:
Who is Participating?
 
Michael FowlerConnect With a Mentor Solutions ConsultantCommented:
Assuming that you will want this function to run against all the part numbers in sheet 1 and not just the first here is the code edited to allow for this

Sub CopyAll()
    Dim lastrow As Long, i As Long
    
    lastrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    For i = 3 To lastrow
        CopyPart Worksheets("Sheet1").Range("A" & i)
    Next
    
End Sub

Sub CopyPart(rng As Range)
    Dim firstRow As Long, stepCount As Long, i As Long
    
    firstRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
    stepCount = rng.Offset(0, 7).Value
    
    Worksheets("Sheet2").Range("A" & firstRow, "A" & firstRow + stepCount - 1).Value = rng.Value
    
    For i = 0 To stepCount - 1
        Worksheets("Sheet2").Range("C" & firstRow + i).Value = Worksheets("Sheet1").Cells(2, i + 9)
    Next
    
End Sub

Open in new window

Copy-and-Paste-Example.xlsm
0
 
gowflowCommented:
Sorry not clear what you want to copy.

Best way is you to fill Sheet2 with the results then will built the VBA accordingly.
gowflow
0
 
EdLBAuthor Commented:
goflow, did you see the attached spreadsheet for illustration? Just using a Macro recording to build the VB won't work because I am using a variable number for the number of columns in Sheet1.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
gowflowCommented:
I didn't say use a macro recording.

I said fill the sheet2 with the result to see an example of how you want the data transferred as your notes and explanation got me now where in understanding what you want.

gowflow
0
 
EdLBAuthor Commented:
See attached file for updated explanation.
Copy-and-Paste-Examplev2.xlsx
0
 
Michael FowlerSolutions ConsultantCommented:
I have updated the code from my last example to include this functionality
Sub CopyPart()
    Dim firstRow As Long, stepCount As Long, i 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
    
    For i = 0 To stepCount - 1
        Worksheets("Sheet2").Range("C" & firstRow + i).Value = Worksheets("Sheet1").Cells(2, i + 9)
    Next
    
End Sub

Open in new window

Copy-and-Paste-Example.xlsm
0
 
EdLBAuthor Commented:
Thanks again and I have one more...
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.

All Courses

From novice to tech pro — start learning today.