| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extend a Range
I have 100 spreadsheets. I defined a range 'MyRange' (insertNamedefine) in
each one of them. 'MyRange' in all the spreadsheets starts in cell A13 and ends somewhere in column D. so in Spreadsheet 1 it could be A13:D30, in spreadsheet 2 it could be A13:D50, etc. I wrote a macro that will open each one of the 100 spreadsheets, copy 'MyRange' and paste it in a Master workbook. I now realised that i should have included column E in my range as well .. so in spreadsheet 1 it should be A13:E30, and so on. I was going to open each one of the spreadsheets and expand the range manually, but then i thought i already have a code that will open each one. All i need is to add a line or 2 in there that changes the range and expands it to include column E before it's copied and pasted. How can i do that in VBA? I'm using Excel 2003. I appreciate your help. Tendresse |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend a Range
this will select the range that you want...
ActiveWorkbook.Names("MyRange").RefersToRange.Resi ze(, 5).Select -- HTH... Jim Thomlinson "Tendresse" wrote: I have 100 spreadsheets. I defined a range 'MyRange' (insertNamedefine) in each one of them. 'MyRange' in all the spreadsheets starts in cell A13 and ends somewhere in column D. so in Spreadsheet 1 it could be A13:D30, in spreadsheet 2 it could be A13:D50, etc. I wrote a macro that will open each one of the 100 spreadsheets, copy 'MyRange' and paste it in a Master workbook. I now realised that i should have included column E in my range as well .. so in spreadsheet 1 it should be A13:E30, and so on. I was going to open each one of the spreadsheets and expand the range manually, but then i thought i already have a code that will open each one. All i need is to add a line or 2 in there that changes the range and expands it to include column E before it's copied and pasted. How can i do that in VBA? I'm using Excel 2003. I appreciate your help. Tendresse |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extend a Range
It worked brilliantly .. thanks a lot :)
"Jim Thomlinson" wrote: this will select the range that you want... ActiveWorkbook.Names("MyRange").RefersToRange.Resi ze(, 5).Select -- HTH... Jim Thomlinson "Tendresse" wrote: I have 100 spreadsheets. I defined a range 'MyRange' (insertNamedefine) in each one of them. 'MyRange' in all the spreadsheets starts in cell A13 and ends somewhere in column D. so in Spreadsheet 1 it could be A13:D30, in spreadsheet 2 it could be A13:D50, etc. I wrote a macro that will open each one of the 100 spreadsheets, copy 'MyRange' and paste it in a Master workbook. I now realised that i should have included column E in my range as well .. so in spreadsheet 1 it should be A13:E30, and so on. I was going to open each one of the spreadsheets and expand the range manually, but then i thought i already have a code that will open each one. All i need is to add a line or 2 in there that changes the range and expands it to include column E before it's copied and pasted. How can i do that in VBA? I'm using Excel 2003. I appreciate your help. Tendresse |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to extend the active range | Excel Discussion (Misc queries) | |||
| Extend Name range | Excel Discussion (Misc queries) | |||
| HOW TO EXTEND A NAMED RANGE | Excel Worksheet Functions | |||
| Painting Range - extend the range on the top part | Excel Discussion (Misc queries) | |||
| Extend Excels range | Excel Programming | |||
Linear Mode
