• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Dynamic Named Ranges from one Worksheet to another

A

alexbell12

New Member
Hi there,


I have around 15 dynamic named ranges comprised of offset formulas in a worksheet and I am trying to copy these formulas into another 30 worksheets without manually rewriting the formulas each time.


Can anyone suggest some code to copy the named ranges (formulas not values) to these worksheets?


Thx,


Alex
 
Hi ,


I am not clear on your requirement. As I understand it , you have defined 15 dynamic named ranges ( say A through O ) in a particular worksheet.


Now , you want to copy these 15 named ranges to 30 other worksheets , without manually rewriting these formulae.


Suppose , for instance one of these formulae is named A , and in the Refers To box , you have :


=OFFSET(Sheet3!$G$9:$P$17,MATCH(Sheet3!$A$24,Sheet3!$E$17:$E$23,0),MATCH(Sheet3!$G$27,Sheet3!$H$24:$H$32,0),COUNTA(Sheet3!$G:$G),3)


If this formula were to be copied to the 30 other worksheets , how would it change ?


Narayan
 
Hi Narayan, thanks for the reply.


Yeah the formulas are not dissimilar to the one you mentioned.


If you directly copy and paste the sheet, it will copy only the result of the formula...e.g. say we copied to a new sheet 'sheet 4', it would only copy ther resulting range, say Sheet4!$E$17:$E$23 instead of the full formula.


This is why i'm looking for a way to do this with vba..
 
Hi ,


You have still not answered my question ; how would the example formula I posted earlier change when it is copied to the other 30 worksheets ?


I think if you want specific answers , you have to be equally specific with your information.


Narayan
 
You must log in or register to reply here.
Back
Top