If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Reference cell in Active Window-possible?
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 |
#2
|
|||
|
|||
Reference cell in Active Window-possible?
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) "Simon" wrote in message ... 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 |
#3
|
|||
|
|||
Reference cell in Active Window-possible?
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) "Simon" wrote in message ... 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 . |
#4
|
|||
|
|||
Reference cell in Active Window-possible?
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 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) "Simon" wrote in message .. . 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 . . |
#5
|
|||
|
|||
Reference cell in Active Window-possible?
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) wrote in message ... 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 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) "Simon" wrote in message .. . 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 . . |
Thread Tools | |
Display Modes | |
|
|