Copy and Paste at set Row counts
 

Hello Pros,

I got a VBA book, I got one, I wont be asking as much!!! Just one more.

At the moment I have a vba that copy and paste values which will change as
future weeks come about. But for this week its range AU7 to AW14. this
range at the moment is copied and the pasted in specific ranges I've placed
in the VBA, but the report is still evolving, new rows with different data
are being added, so every time I add a row, I have to change the row range
about 150 times. Is there a way, since the report has a specific format,
where it can just paste this initial copied pasgte of AU7:AW14 every 18 rows,
so if I add new rows, all I have to do is change the row count on the VBA,
not as difficult. Hope you understand and could help guys.

Regards,

Copy and Paste at set Row counts
 

Yes, but I don't understand your situation well enough to make a specific
recommendation. Is the range AU7:AW14 stable? Will it change? I guess it is
not clear to me what rows change. Where do the "new rows" get added and what
"row range" do you change, and how?

"JavyD" wrote in message
...
Hello Pros,

I got a VBA book, I got one, I wont be asking as much!!! Just one more.

At the moment I have a vba that copy and paste values which will change as
future weeks come about. But for this week its range AU7 to AW14. this
range at the moment is copied and the pasted in specific ranges I've
placed
in the VBA, but the report is still evolving, new rows with different data
are being added, so every time I add a row, I have to change the row range
about 150 times. Is there a way, since the report has a specific format,
where it can just paste this initial copied pasgte of AU7:AW14 every 18
rows,
so if I add new rows, all I have to do is change the row count on the VBA,
not as difficult. Hope you understand and could help guys.

Regards,

Copy and Paste at set Row counts
 

Bob this is what a portion of the VBA that I need to fix looks like. As you
can see, this has a large Range, not as large as others that I haveAs you can
see, the ranges are 47 rows apart.

Range("AU9:AU11").Select
Selection.Copy
Range( _

"AU9,AU56,AU103,AU150,AU197,AU244,AU291,AU338,AU38 5,AU432,AU479,AU526,AU573,AU620" _
).Select
Range("AU620").Activate
ActiveSheet.paste

"Bob Kilmer" wrote:

Yes, but I don't understand your situation well enough to make a specific
recommendation. Is the range AU7:AW14 stable? Will it change? I guess it is
not clear to me what rows change. Where do the "new rows" get added and what
"row range" do you change, and how?

"JavyD" wrote in message
...
Hello Pros,

I got a VBA book, I got one, I wont be asking as much!!! Just one more.

At the moment I have a vba that copy and paste values which will change as
future weeks come about. But for this week its range AU7 to AW14. this
range at the moment is copied and the pasted in specific ranges I've
placed
in the VBA, but the report is still evolving, new rows with different data
are being added, so every time I add a row, I have to change the row range
about 150 times. Is there a way, since the report has a specific format,
where it can just paste this initial copied pasgte of AU7:AW14 every 18
rows,
so if I add new rows, all I have to do is change the row count on the VBA,
not as difficult. Hope you understand and could help guys.

Regards,

Copy and Paste at set Row counts
 

Due to the lack of further info i think he just needs to copy the
amount of found rows elsewhere and wants to have low maintenance.

Easy way in VBA would be to dynamically set the range, something like
this:
Range("AU7:AU" & LastFilledRownumberVariableHere).select
Selection.Copy

This way u dont need to do any manual changing of range everytime the
amount of rows change. Just make a loop to check for the last filled
row and asign it to a variable.

Cheers,
LM

"Bob Kilmer" wrote in message ...
Yes, but I don't understand your situation well enough to make a specific
recommendation. Is the range AU7:AW14 stable? Will it change? I guess it is
not clear to me what rows change. Where do the "new rows" get added and what
"row range" do you change, and how?

"JavyD" wrote in message
...
Hello Pros,

I got a VBA book, I got one, I wont be asking as much!!! Just one more.

At the moment I have a vba that copy and paste values which will change as
future weeks come about. But for this week its range AU7 to AW14. this
range at the moment is copied and the pasted in specific ranges I've
placed
in the VBA, but the report is still evolving, new rows with different data
are being added, so every time I add a row, I have to change the row range
about 150 times. Is there a way, since the report has a specific format,
where it can just paste this initial copied pasgte of AU7:AW14 every 18
rows,
so if I add new rows, all I have to do is change the row count on the VBA,
not as difficult. Hope you understand and could help guys.

Regards,