Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying row data through a macro
I have a macro I am working on which creates a report based on the Investors
data in a main sheet. The macro works fine on the first name, it copies and pastes all the headers and graphs and then a row of investor info from the main sheet. The problem lies in that the loop thru the investor names is always using the same investor's data from that row (duh, that row is hardcoded ). How do i get the row data to increment down using offset when you are offsetting a whole row range, ie a3:i3. I tried to use the variable i from the loop ie, Range("A(i):I(i)"), obviously that did not work. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying row data through a macro
<<I tried to use the variable i rom the loop ie, Range("A(i):I(i)"),
obviously that did not work That is a syntax issue...Try the below For i = 1 To 10 Range("A" & i & ":I" & i).Select Next If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I have a macro I am working on which creates a report based on the Investors data in a main sheet. The macro works fine on the first name, it copies and pastes all the headers and graphs and then a row of investor info from the main sheet. The problem lies in that the loop thru the investor names is always using the same investor's data from that row (duh, that row is hardcoded ). How do i get the row data to increment down using offset when you are offsetting a whole row range, ie a3:i3. I tried to use the variable i from the loop ie, Range("A(i):I(i)"), obviously that did not work. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying row data through a macro
I'm testing that like this
Sub test() For Each c In Range("InvestorNames") Range("A" & c & ":I" & c).Select 'need to set this line up as variable so loop will offset Application.CutCopyMode = False Selection.Copy next c End Sub It doesn't select the range. Eyeballing it shouldn't the range have " at front and " at back? I don't see how your syntax puts it in that form "Jacob Skaria" wrote: <<I tried to use the variable i rom the loop ie, Range("A(i):I(i)"), obviously that did not work That is a syntax issue...Try the below For i = 1 To 10 Range("A" & i & ":I" & i).Select Next If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I have a macro I am working on which creates a report based on the Investors data in a main sheet. The macro works fine on the first name, it copies and pastes all the headers and graphs and then a row of investor info from the main sheet. The problem lies in that the loop thru the investor names is always using the same investor's data from that row (duh, that row is hardcoded ). How do i get the row data to increment down using offset when you are offsetting a whole row range, ie a3:i3. I tried to use the variable i from the loop ie, Range("A(i):I(i)"), obviously that did not work. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying row data through a macro
Jacob,
It does work. I had to se a new cntr variable as "c" contained strings. It works now thank you "Jacob Skaria" wrote: <<I tried to use the variable i rom the loop ie, Range("A(i):I(i)"), obviously that did not work That is a syntax issue...Try the below For i = 1 To 10 Range("A" & i & ":I" & i).Select Next If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I have a macro I am working on which creates a report based on the Investors data in a main sheet. The macro works fine on the first name, it copies and pastes all the headers and graphs and then a row of investor info from the main sheet. The problem lies in that the loop thru the investor names is always using the same investor's data from that row (duh, that row is hardcoded ). How do i get the row data to increment down using offset when you are offsetting a whole row range, ie a3:i3. I tried to use the variable i from the loop ie, Range("A(i):I(i)"), obviously that did not work. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying row data through a macro
You dont need to use offset....InvestorNames is A1:J100..The below will loop
through each row. and copy each row to Sheet2; row (y). Another point is to copy cells you dont need to select instead you can directly copy the range as below.. Sub test() y = 1 For Each C In Range("InvestorNames").Rows Range("A" & C.Row & ":I" & C.Row).Copy Worksheets("Sheet2").Range("A" & y) y = y + 1 Next C End Sub If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I'm testing that like this Sub test() For Each c In Range("InvestorNames") Range("A" & c & ":I" & c).Select 'need to set this line up as variable so loop will offset Application.CutCopyMode = False Selection.Copy next c End Sub It doesn't select the range. Eyeballing it shouldn't the range have " at front and " at back? I don't see how your syntax puts it in that form "Jacob Skaria" wrote: <<I tried to use the variable i rom the loop ie, Range("A(i):I(i)"), obviously that did not work That is a syntax issue...Try the below For i = 1 To 10 Range("A" & i & ":I" & i).Select Next If this post helps click Yes --------------- Jacob Skaria "thomas donino" wrote: I have a macro I am working on which creates a report based on the Investors data in a main sheet. The macro works fine on the first name, it copies and pastes all the headers and graphs and then a row of investor info from the main sheet. The problem lies in that the loop thru the investor names is always using the same investor's data from that row (duh, that row is hardcoded ). How do i get the row data to increment down using offset when you are offsetting a whole row range, ie a3:i3. I tried to use the variable i from the loop ie, Range("A(i):I(i)"), obviously that did not work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Copying Data | Excel Programming | |||
A better Macro for Copying Data | Excel Programming | |||
Macro for copying conditional data | Excel Worksheet Functions | |||
Macro Help - copying specific data | Excel Programming | |||
Copying data ranges in a macro | Excel Programming |