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

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

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