Solved

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

Posted on 2015-01-27
7
323 Views
Last Modified: 2015-01-27
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
0
Comment
Question by:EdLB
  • 3
  • 2
  • 2
7 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40573788
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
 

Author Comment

by:EdLB
ID: 40573838
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40573875
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:EdLB
ID: 40574127
See attached file for updated explanation.
Copy-and-Paste-Examplev2.xlsx
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40574181
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
 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
ID: 40574197
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
 

Author Closing Comment

by:EdLB
ID: 40574446
Thanks again and I have one more...
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now