Reference cell in Active Window-possible?

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

EXCEL 2002

My workbook is used to calculate and then print out bills
for each room in our hotel of six rooms. Thus I have seven
worksheets, six identical worksheets being one for each
room (named 101,102,103 etc), and a seventh sheet (named
CustCopy) being the actual printed out page that we give
to the customer.

I have a command button on each of the six "room" sheets
which when clicked prints out the seventh sheet. The
command button VB is

Private Sub CommandButton2_Click()

Sheets(Array("102", "CustCopy")).Select
Sheets("102").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Sheets("102").Select
End Sub

obviously this is the command button on our worksheet for
room 102.

At the moment the cell references on the CustCopy sheet
refer only to the room 101 sheet, for example a cell
thereon is

='101'!C6

so this duly prints out the contents of cell C6 on
worksheet 101, and always will regardless of which
actual "room" worksheet we are actually on. Is there some
way to make the cells on this worksheet reference the
cells from the room worksheet we're actually trying to
print from? I tried for example to change the cell
references in the CustCopy sheet to e.g.

=[ActiveWindow]!C6

but no joy.

I appreciate that I could just create a
designated "printout" sheet for each room worksheet but
that is very unwieldy.

Thanks very much if someone has the answer.

Simon
 
Hi Simon,

One way could be to put a title in say Cell Aq1 of "Room #", and in B1 put
the room number. This can be shown in the printout, but more importantly you
can use B1 in the formula, like this

=INDIRECT("'" & B1 & "'!C6")

and then just change B1 to the room in question.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
Thanks for replying - I've rambled the Purbecks!

I'm not sure your indirect idea works. Like my previous
explanation, my workbook consists of 7 worksheets, 6 being
identical ones for each room and the seventh [CustCopy]
being the actual printout of the bill we give to the
client.

Obviously this CustCopy worksheet references the cells on
the Room worksheet and it needs to reference the cells
only on that particular Room worksheet from which the user
clicks the "Print This Bill" command button. So if the
user happens to have the Room 103 worksheet active when he
clicks its "Print This Bill" command button, then the
formulas in the cells on the CustCopy worksheet need to be
filled in with the data from the cells on said Room 103
worksheet.

I believe it is just a matter of getting the formulas in
the cells on the CustCopy worksheet to realise that they
need to look to the active worksheet for their data (i.e.
from where the user clicked the print command button). I
tried something like:
=[ActiveWindow]!C6
as an example of a cell reference in the CustCopy
worksheet to print the contents of cell C6 on the Room
worksheet which is active.

Thanks a lot for trying to help - do tell me if I'm taking
up too much of your time now.
Simon (from England though currently sojourning in Quebec)
-----Original Message-----
Hi Simon,

One way could be to put a title in say Cell Aq1 of "Room #", and in B1 put
the room number. This can be shown in the printout, but more importantly you
can use B1 in the formula, like this

=INDIRECT("'" & B1 & "'!C6")

and then just change B1 to the room in question.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

EXCEL 2002

My workbook is used to calculate and then print out bills
for each room in our hotel of six rooms. Thus I have seven
worksheets, six identical worksheets being one for each
room (named 101,102,103 etc), and a seventh sheet (named
CustCopy) being the actual printed out page that we give
to the customer.

I have a command button on each of the six "room" sheets
which when clicked prints out the seventh sheet. The
command button VB is

Private Sub CommandButton2_Click()

Sheets(Array("102", "CustCopy")).Select
Sheets("102").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Sheets("102").Select
End Sub

obviously this is the command button on our worksheet for
room 102.

At the moment the cell references on the CustCopy sheet
refer only to the room 101 sheet, for example a cell
thereon is

='101'!C6

so this duly prints out the contents of cell C6 on
worksheet 101, and always will regardless of which
actual "room" worksheet we are actually on. Is there some
way to make the cells on this worksheet reference the
cells from the room worksheet we're actually trying to
print from? I tried for example to change the cell
references in the CustCopy sheet to e.g.

=[ActiveWindow]!C6

but no joy.

I appreciate that I could just create a
designated "printout" sheet for each room worksheet but
that is very unwieldy.

Thanks very much if someone has the answer.

Simon
Click to expand...


.
Click to expand...
 
Bob, just to say I solved my problem by blanking all the
cells on the printout sheet and having print command
button code fill them in as appropriate from the "room"
worksheets.
Thanks again for your input
Simon
-----Original Message-----
Bob,
Thanks for replying - I've rambled the Purbecks!

I'm not sure your indirect idea works. Like my previous
explanation, my workbook consists of 7 worksheets, 6 being
identical ones for each room and the seventh [CustCopy]
being the actual printout of the bill we give to the
client.

Obviously this CustCopy worksheet references the cells on
the Room worksheet and it needs to reference the cells
only on that particular Room worksheet from which the user
clicks the "Print This Bill" command button. So if the
user happens to have the Room 103 worksheet active when he
clicks its "Print This Bill" command button, then the
formulas in the cells on the CustCopy worksheet need to be
filled in with the data from the cells on said Room 103
worksheet.

I believe it is just a matter of getting the formulas in
the cells on the CustCopy worksheet to realise that they
need to look to the active worksheet for their data (i.e.
from where the user clicked the print command button). I
tried something like:
=[ActiveWindow]!C6
as an example of a cell reference in the CustCopy
worksheet to print the contents of cell C6 on the Room
worksheet which is active.

Thanks a lot for trying to help - do tell me if I'm taking
up too much of your time now.
Simon (from England though currently sojourning in Quebec)
-----Original Message-----
Hi Simon,

One way could be to put a title in say Cell Aq1
Click to expand...
of "Room
#", and in B1 put
the room number. This can be shown in the printout, but more importantly you
can use B1 in the formula, like this

=INDIRECT("'" & B1 & "'!C6")

and then just change B1 to the room in question.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

EXCEL 2002

My workbook is used to calculate and then print out bills
for each room in our hotel of six rooms. Thus I have seven
worksheets, six identical worksheets being one for each
room (named 101,102,103 etc), and a seventh sheet (named
CustCopy) being the actual printed out page that we give
to the customer.

I have a command button on each of the six "room" sheets
which when clicked prints out the seventh sheet. The
command button VB is

Private Sub CommandButton2_Click()

Sheets(Array("102", "CustCopy")).Select
Sheets("102").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Sheets("102").Select
End Sub

obviously this is the command button on our worksheet for
room 102.

At the moment the cell references on the CustCopy sheet
refer only to the room 101 sheet, for example a cell
thereon is

='101'!C6

so this duly prints out the contents of cell C6 on
worksheet 101, and always will regardless of which
actual "room" worksheet we are actually on. Is there some
way to make the cells on this worksheet reference the
cells from the room worksheet we're actually trying to
print from? I tried for example to change the cell
references in the CustCopy sheet to e.g.

=[ActiveWindow]!C6

but no joy.

I appreciate that I could just create a
designated "printout" sheet for each room worksheet but
that is very unwieldy.

Thanks very much if someone has the answer.

Simon
Click to expand...


.
Click to expand...
.
Click to expand...
 
Pleasure, and I hope you enjoyed the Purbecks in your ramblings.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob, just to say I solved my problem by blanking all the
cells on the printout sheet and having print command
button code fill them in as appropriate from the "room"
worksheets.
Thanks again for your input
Simon
-----Original Message-----
Bob,
Thanks for replying - I've rambled the Purbecks!

I'm not sure your indirect idea works. Like my previous
explanation, my workbook consists of 7 worksheets, 6 being
identical ones for each room and the seventh [CustCopy]
being the actual printout of the bill we give to the
client.

Obviously this CustCopy worksheet references the cells on
the Room worksheet and it needs to reference the cells
only on that particular Room worksheet from which the user
clicks the "Print This Bill" command button. So if the
user happens to have the Room 103 worksheet active when he
clicks its "Print This Bill" command button, then the
formulas in the cells on the CustCopy worksheet need to be
filled in with the data from the cells on said Room 103
worksheet.

I believe it is just a matter of getting the formulas in
the cells on the CustCopy worksheet to realise that they
need to look to the active worksheet for their data (i.e.
from where the user clicked the print command button). I
tried something like:
=[ActiveWindow]!C6
as an example of a cell reference in the CustCopy
worksheet to print the contents of cell C6 on the Room
worksheet which is active.

Thanks a lot for trying to help - do tell me if I'm taking
up too much of your time now.
Simon (from England though currently sojourning in Quebec)
-----Original Message-----
Hi Simon,

One way could be to put a title in say Cell Aq1
Click to expand...
of "Room
#", and in B1 put
the room number. This can be shown in the printout, but more importantly you
can use B1 in the formula, like this

=INDIRECT("'" & B1 & "'!C6")

and then just change B1 to the room in question.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

EXCEL 2002

My workbook is used to calculate and then print out bills
for each room in our hotel of six rooms. Thus I have seven
worksheets, six identical worksheets being one for each
room (named 101,102,103 etc), and a seventh sheet (named
CustCopy) being the actual printed out page that we give
to the customer.

I have a command button on each of the six "room" sheets
which when clicked prints out the seventh sheet. The
command button VB is

Private Sub CommandButton2_Click()

Sheets(Array("102", "CustCopy")).Select
Sheets("102").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Sheets("102").Select
End Sub

obviously this is the command button on our worksheet for
room 102.

At the moment the cell references on the CustCopy sheet
refer only to the room 101 sheet, for example a cell
thereon is

='101'!C6

so this duly prints out the contents of cell C6 on
worksheet 101, and always will regardless of which
actual "room" worksheet we are actually on. Is there some
way to make the cells on this worksheet reference the
cells from the room worksheet we're actually trying to
print from? I tried for example to change the cell
references in the CustCopy sheet to e.g.

=[ActiveWindow]!C6

but no joy.

I appreciate that I could just create a
designated "printout" sheet for each room worksheet but
that is very unwieldy.

Thanks very much if someone has the answer.

Simon


.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top