pasting an array to the next blank location...

[Andymann]

I have an array of data B2:H183. I wish to write a macro that will select the array, and copy it to J2:P183. That's easy. My problem is, next week, I want to use the same macro, but the array must be copied to R2:X183. The following week it must be copied to Z2. And so on. I've tried IF and AND statements, but this only seems to work up to a point.
Any other suggestions?

Thanks, much appreciated!

Andymann

 

[rikrak]

On 2002-09-17 01:42, Andymann wrote:
I have an array of data B2:H183. I wish to write a macro that will select the array, and copy it to J2:P183. That's easy. My problem is, next week, I want to use the same macro, but the array must be copied to R2:X183. The following week it must be copied to Z2. And so on. I've tried IF and AND statements, but this only seems to work up to a point.
Any other suggestions?

Thanks, much appreciated!

Andymann

Hi Andymann,

this macro will do it:
Sub copyWeekly()
Range("B2:H183").Copy
'find last cell in use in row 2 and select 2 cells to the right
Range("IV2").End(xlToLeft).Offset(0, 2).Select
ActiveSheet.Paste
End Sub

Note: H2 must contain something

 

[Andymann]

That it will!

Thanks, greatly appreciated!

Enjoy your day further.

Cheers

Andymann