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.

<<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.

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.

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.

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.