VB Scripting/Macro - Filling/Editing Worksheet References

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Meghan - 16 Dec 2004 16:05 GMT

Question:

What VB code will allow me to rewrite just the worksheet reference in a
function/cell reference. For example it says

=January!$AH40

and I would like a macro that would change it to

=February!$AH40

Any suggestions would be greatly appreciated!!! :)

Background:

I have 12 cells in a row that each refer to the same cell location on twelve
different worksheets within the same workbook.

The fill function does not "fill" the worksheet reference. It is too time
consuming to manually input or edit 2016 cells.

Reply to this Message

Frank Kabel - 16 Dec 2004 16:08 GMT

Hi
you may have a look at the INDIRECT function for this

Signature

Regards
Frank Kabel
Frankfurt, Germany

> Question:
>
[quoted text clipped - 17 lines]
> The fill function does not "fill" the worksheet reference. It is too time
> consuming to manually input or edit 2016 cells.

Reply to this Message

Peo Sjoblom - 16 Dec 2004 17:55 GMT

This will change the sheet name when copied down

=INDIRECT("'"&INDEX({"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},ROW(1:1))&"'!AH40")

assuming the 12 sheets are named after the months

Regards,

Peo Sjoblom

> Question:
>
[quoted text clipped - 16 lines]
> The fill function does not "fill" the worksheet reference. It is too time
> consuming to manually input or edit 2016 cells.

Reply to this Message