Dynamic Range of Columns with fixed Rows

**nms2130** · 01-05-2009, 11:07 AM

I am still trying to develop a macro that will copy and paste a set of formulas into a dynamic destination range. I have included a worksheet that shows what I am trying to do. Basically when using AutoFill I need the columns of the destination range to be dynamic based on the number of rows in another sheet.

Please Login or Register  to view this content.

So if the number of rows is determined by a COUNTA formula that returns a result of 3, the destination range will be "D42:F68", if 94 , "D42:CS68".
Also I would like to change the Selection to be a fixed named range such as "ACCT".

Is this possible?

**royUK** · 01-05-2009, 11:40 AM

There is no attachment

**nms2130** · 01-05-2009, 12:18 PM

I apologize. The attachment should be included now. It is very simplistic, but I think it helps to explain my goal.

**royUK** · 01-05-2009, 12:39 PM

It's still not clear what you want.

**nms2130** · 01-05-2009, 01:02 PM

I am trying to define a dynamic range in the macro, either for a copy and paste or an auto fill macro. I cannot figure out how to make the ranges variable in the macro, rather than fixed.
The number of columns to paste across is currently determined by a COUNTA formula that is counting the number of rows with information on another sheet. For instance if I have formulae in range A1:A15 and in another column I have data in 10 rows, the macro will copy A1:A15 and paste the formulae into B1:J15.
J is determined as being the 10th column.
The problem is making the Range("_:_") part of the macro dynamic.

Any ideas?

Thanks for your patience.

**GuruWannaB** · 01-05-2009, 02:51 PM

I changed your example spreadsheet a bit - to make your NUMBER OF COLUMNS data not in the same rows of where the formulas where being copied to.

I created a macro TEST that copies and INSERTS the over the number of columns to the right of the originating FORMULA range for the number of rows found in your NUMBER OF COLUMNS data range. Note this is inserting the text...however you can tweek it for formulas

**nms2130** · 01-05-2009, 04:18 PM

What are the 5 and 3 referring to?

Range(Cells(5, 3)

**GuruWannaB** · 01-05-2009, 04:23 PM

Originally Posted by nms2130

What are the 5 and 3 referring to?

Range(Cells(5, 3)

"Cells(5,3)" refers to 5th row, 3rd column - otherwise known as C5. I did it in the R1C1 format so you can make the Range dynamic..

**nms2130** · 01-05-2009, 04:40 PM

I understand now. Is it possible to have the copied range pasted over the selected range? When inserting as it is, an error is returned because it is pushing cells off of the sheet.

**GuruWannaB** · 01-05-2009, 04:44 PM

Please Login or Register  to view this content.

I changed the last two lines of code...

Note...if you were originally getting an error message for pushing cells off the sheet...you might have data that was in those cells that was triggering the message. With that being said....you potentially have the issue of those cells being written over with a paste functionality if it copies that many columns over.

**nms2130** · 01-05-2009, 04:57 PM

Error
Object doesn't support this property or method
Range(Cells(42, 4), Cells(15, Srange)).Paste

**GuruWannaB** · 01-05-2009, 04:57 PM

My mistake...review the code again above...I corrected it

**nms2130** · 01-05-2009, 05:21 PM

This is great! Thank you, it is working great now!

**VBA Noob** · 01-05-2009, 05:24 PM

Please edit you're post to include code tags as per forum rules below

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save