Extend a Range

  • Thread starter Thread starter Tendresse
  • Start date Start date
T

Tendresse

I have 100 spreadsheets. I defined a range 'MyRange' (insert>Name>define) 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
 
this will select the range that you want...

ActiveWorkbook.Names("MyRange").RefersToRange.Resize(, 5).Select
 
It worked brilliantly .. thanks a lot :)

Jim Thomlinson said:
this will select the range that you want...

ActiveWorkbook.Names("MyRange").RefersToRange.Resize(, 5).Select
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove specific cells in a column. 2
Combining Multilple Worksheets Into One 6
Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names 2
Problem using Range objects 1
MS Excel VBA Worksheets group tables in one table. 1
Copy down 1 column to the left 3
Conditional Testing of 3 cells 4
Excel macro error when workbook is shared 0

Back
Top