G
Guest
Basic question: Is there any way to effectively cut and paste from the Name
Manager (maybe through visual basic) into another application for quicker
editing?
Specifics: I have a complicated spreadsheet with multiple worksheets. But
the data on each worksheet is very similar in layout. I also have a sheet
where I chart various things from the data sheets page to compare them.
I'm trying to set up dynamic charts so Excel only plots the most recent 20
points in each row. There are about 100 rows per worksheet.
I have the dynamic charts working now for the first worksheet, and it would
be really easy to cut and paste from the view in the name manager, i.e., the
Name and Refers To columns, into a Word document. Then in Word do a search
and replace of the worksheet names (which I also embedded into the Names of
the first set of charts), and then cut and paste the new Names and Refers To
formulas back into the Name Manager.
For example, my current names look like this:
Sheet1_Values0 =OFFSET(Sheet1!$C1$1,0,COUNTA(Sheet1!$C:$C)-20,,20)
Sheet1_Values1 =OFFSET(Sheet1!$C1$1,1,COUNTA(Sheet1!$C:$C)-20,,20)
....
Sheet1_Valuesn =OFFSET(Sheet1!$C1$1,n,COUNTA(Sheet1!$C:$C)-20,,20)
But now I just want a duplicate of this but all with Sheet2 instead of Sheet
1 in both the Names and Refers To columns. A very simple task in Word (or
any other editor). If I could then cut and paste these back in Excel's Name
Manager, I'd be all set.
It was a long and tedious effort to enter these manually in the name
manager, and now it will be another long and tedious to duplicate them for
the other sheets.
There must me an easier way. I suspect there is through VB. I've never
done visual basic, but I do know programming. I looked quickly through help
and on the internet, but cut and paste and / or names are used is so many
contexts, it's hard to weed out things to find a solution to this problem.
Can somebody please point me in the right direction?
Thanks in advance!!!
Manager (maybe through visual basic) into another application for quicker
editing?
Specifics: I have a complicated spreadsheet with multiple worksheets. But
the data on each worksheet is very similar in layout. I also have a sheet
where I chart various things from the data sheets page to compare them.
I'm trying to set up dynamic charts so Excel only plots the most recent 20
points in each row. There are about 100 rows per worksheet.
I have the dynamic charts working now for the first worksheet, and it would
be really easy to cut and paste from the view in the name manager, i.e., the
Name and Refers To columns, into a Word document. Then in Word do a search
and replace of the worksheet names (which I also embedded into the Names of
the first set of charts), and then cut and paste the new Names and Refers To
formulas back into the Name Manager.
For example, my current names look like this:
Sheet1_Values0 =OFFSET(Sheet1!$C1$1,0,COUNTA(Sheet1!$C:$C)-20,,20)
Sheet1_Values1 =OFFSET(Sheet1!$C1$1,1,COUNTA(Sheet1!$C:$C)-20,,20)
....
Sheet1_Valuesn =OFFSET(Sheet1!$C1$1,n,COUNTA(Sheet1!$C:$C)-20,,20)
But now I just want a duplicate of this but all with Sheet2 instead of Sheet
1 in both the Names and Refers To columns. A very simple task in Word (or
any other editor). If I could then cut and paste these back in Excel's Name
Manager, I'd be all set.
It was a long and tedious effort to enter these manually in the name
manager, and now it will be another long and tedious to duplicate them for
the other sheets.
There must me an easier way. I suspect there is through VB. I've never
done visual basic, but I do know programming. I looked quickly through help
and on the internet, but cut and paste and / or names are used is so many
contexts, it's hard to weed out things to find a solution to this problem.
Can somebody please point me in the right direction?
Thanks in advance!!!