EdLB
asked on
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
See attached spreadsheet for illustration.
'Copy row of value of length NrOpns from "Sheet1" into "Sheet2"
Worksheets("Sheet2").Range
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
ASKER
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.
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
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
ASKER
See attached file for updated explanation.
Copy-and-Paste-Examplev2.xlsx
Copy-and-Paste-Examplev2.xlsx
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
Copy-and-Paste-Example.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again and I have one more...
Best way is you to fill Sheet2 with the results then will built the VBA accordingly.
gowflow