Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / July 2007

Tip: Looking for answers? Try searching our database.

Want to Printing Sheet(x) from Sheet1

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Manuel Davila - 07 Jul 2007 03:41 GMT
Greetings,

I would like to print a Worksheet from another active worksheet. Example:
From Sheet1: (clicking a cell, button??)
If condition 2=true print Sheet2
If condition 3=true print Sheet3 etc

Objective: Printing Bank Deposits Slips: We have 5 different accounts in one
Bank, we have created 5 protected worksheets each having the fixed data on
them (acc# ,acc name, etc). The variable data (deposits, cash back etc) we
enter them from  Sheet1 using a list we send the data to the corresponding
account Sheet(2-6). We want to print only the Sheet that we selected in the
list field. Is this possible?

TIA for your help,

Manuel
Zone - 08 Jul 2007 14:36 GMT
Manuel, Here's one way to do it.  Say your account number is in column
A of Sheet 1, deposit amount in column B, cash back in column C.  And,
you want to put the deposit amount in cell B3 of the deposit slip,
cash back in cell B5 (regardless of which deposit slip is being used).
-  Display Sheet 1 on the screen and press Alt-F11 to show the code
editor.
-  If there isn't a code module in the editor, click Insert, then
Module.
-  Copy the code below and paste it in the module.
-  Change the numbers in the subroutine (such as Case 203089) to show
the actual account number that should refer to each sheet.
-  Change the cell addresses as necessary to the cells from which to
get the data and to which to paste it.
-  Press Alt-F11 to return to Sheet 1.
-  Right-click on the toolbar and select Forms.
-  Click on the button icon on the Forms toolbar, then click on the
sheet where you want to put the button.
-  Click away from the button, then right-click on the button and
select Assign Macro.
-  Choose PrintSht from the list and click OK.
-  Click away from the button.
-  Save the file.

Now, you can fill out the information on Sheet 1.  Select any cell on
the row you want to print a deposit slip for.  Click the button.
Hope this works for you!  James

Sub PrintSht()
   Dim mySht As Integer
   Select Case Cells(ActiveCell.Row, "a")
       Case 203089: mySht = 2
       Case 206988: mySht = 3
       Case 150952: mySht = 4
       Case 308942: mySht = 5
       Case 112038: mySht = 6
   End Select
   With Worksheets(mySht)
       .Cells(3, "b") = Cells(ActiveCell.Row, "b")
       .Cells(5, "b") = Cells(ActiveCell.Row, "c")
       .PrintOut
   End With
End Sub

> Greetings,
>
[quoted text clipped - 13 lines]
>
> Manuel
Manuel Davila - 09 Jul 2007 13:01 GMT
James,
WOW! way over my head, but the solution looks elegant. It was about time for
me to explore the insides of Excel. I'll try to implement it. Back to you
later.
Thank you very much for your time and prompt response
Manuel

> Manuel, Here's one way to do it.  Say your account number is in column
> A of Sheet 1, deposit amount in column B, cash back in column C.  And,
[quoted text clipped - 61 lines]
>>
>> Manuel
Manuel Davila - 09 Jul 2007 14:17 GMT
James,
Yes, yes!!!! It works flawlessly! Thanks again
Manuel

> Manuel, Here's one way to do it.  Say your account number is in column
> A of Sheet 1, deposit amount in column B, cash back in column C.  And,
[quoted text clipped - 61 lines]
>>
>> Manuel
Zone - 09 Jul 2007 15:02 GMT
Manuel, Glad to help!  James

> James,
> Yes, yes!!!! It works flawlessly! Thanks again
[quoted text clipped - 67 lines]
>
> - Show quoted text -

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.