set ranges in Excel using VBA
Posted on 2015-02-19
I have a spreadsheet that has numerous set named ranges, so in Name Manager for instance I would like to call row AB, AB and the range I need is ='Raw Data'!$AB1:$AB64000 [I have 17 Ranges I need to set]
I have tried to make this work through VBA but failed miserably. I tried
ActiveWorkbook.Names("AB").RefersToR1C1 = "='Raw Data'!RC28:RC28"
but when I ran this it returned an error "Application defined or object defined error"
Is there a way to get VBA to put these ranges in and make sure that when the new data is entered in the Raw Data sheet, the ranges do not change.
I say that because when I set up the ranges manually in Name Manager every time I put data in the Raw Data sheet the ranges did change. i.e. when I run the macro the first time the Raw data tab may have 1000 items next time 20000 items next time 50000 items. So that is why the ranges must remain the same every time.