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 -