?
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
Medium Priority
?
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 31

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 31

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

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

Expert Comment

by:Michael Fowler
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:
Michael Fowler earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
Suggested Courses

743 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