Creating summary worksheet from template

[Ballwin Tom]

Thanks in advance for any help on this. I'm using Excel 2003. (I have Access
2003 as well if that would help, make this easy.)
I have a simple template that helps employees count the cash in our two cash
registers. Simply put in the number of $20's, $10's, rolls of coins etc and
you get a total by till. Then the employee imputs the cash amount the
register thinks should be there and the result shows if the register is over
or under. There's a date/time field (using NOW()) and a drop down box for the
employees name.
Each time the template is used I want the employee name, the date/time, and
two of the totals for each till to go into a summary worksheet. Everyday we
count the till at least 3 times with different mgrs each time. Once the
template has updated the summary worksheet the template can close and clear
the data.
Suggestions?

 

[StumpedAgain]

One option is to write a quick macro that you can assign to a button that
will copy this information to the 'Summary' sheet. If you give some
specifics (cell locations, where you want what, etc.) I can write it up for
you right quick.

 

[Ballwin Tom]

Thank you for your VERY generous offer! In my workbook I have two sheets:
TillCounter and TillSummary. In the TillCounter, I have named the cells I
want to go to the TillSummary sheet. They are: Date (H2), MGRnow(C2),
TillT1(D25), OvUn1(D29), TillT2(I25), OvUn2(129). These names (in this order)
are the column headers in the Till Summary worksheet.
I would like to have the functionality of the TillCounter sheet to be like a
template where each time it opens it's cleared of all data. If I need to make
the TillSummary a seperate workbook, I will.
Thanks again, I look forward to seeing how easy this was.

 

[Ballwin Tom]

Thank you for your VERY generous offer! In my workbook I have two sheets:
TillCounter and TillSummary. In the TillCounter, I have named the cells I
want to go to the TillSummary sheet. They are: Date (H2), MGRnow(C2),
TillT1(D25), OvUn1(D29), TillT2(I25), OvUn2(129). These names (in this order)
are the column headers in the Till Summary worksheet.
I would like to have the functionality of the TillCounter sheet to be like a
template where each time it opens it's cleared of all data. If I need to make
the TillSummary a seperate workbook, I will.
Thanks again, I look forward to seeing how easy this was.

 

[StumpedAgain]

Hi Tom,

If you haven't used macros before, read the following. Otherwise, just copy
the macro into a module, assign it to a button, and go to town! Also note
that I guessed on where your employees are putting the number of 20's, 10's
etc so you might have to tweak that a little in the ".clearcontents" range.

To start a new macro:
Open excel and press Alt + F11
Start a new module by going to Insert->Module
Paste the below programming into the module.

To create a new button:
On the Tools menu, click Customize.
Click the Commands tab.
In the Categories box, drag "Forms" to the toolbar.
Select "Button" from the new toolbar.
Click and drag where you want your button to be placed.
Select the "TillSummary" macro and press "OK".
Re-name or format the Button.
Use the button!

'(Mind any text wrapping - if text appears in red in the macro, put the
small lines with the previous line)

Option Explicit
Sub TillSummary()

If Sheets("TillSummary").Range("A2") = "" Then

Range("Date").Copy Destination:=Sheets("TillSummary").Range("A2")
Range("MGRnow").Copy Destination:=Sheets("TillSummary").Range("B2")
Range("TillT1").Copy Destination:=Sheets("TillSummary").Range("C2")
Range("OvUn1").Copy Destination:=Sheets("TillSummary").Range("D2")
Range("TillT2").Copy Destination:=Sheets("TillSummary").Range("E2")
Range("OvUn2").Copy Destination:=Sheets("TillSummary").Range("F2")

Else:

Range("Date").Copy Destination:=Sheets("TillSummary") _
..Range("A1").End(xlDown).Offset(1, 0)
Range("MGRnow").Copy Destination:=Sheets("TillSummary") _
..Range("B1").End(xlDown).Offset(1, 0)
Range("TillT1").Copy Destination:=Sheets("TillSummary") _
..Range("C1").End(xlDown).Offset(1, 0)
Range("OvUn1").Copy Destination:=Sheets("TillSummary") _
..Range("D1").End(xlDown).Offset(1, 0)
Range("TillT2").Copy Destination:=Sheets("TillSummary") _
..Range("E1").End(xlDown).Offset(1, 0)
Range("OvUn2").Copy Destination:=Sheets("TillSummary") _
..Range("F1").End(xlDown).Offset(1, 0)

End If

Range("C324").ClearContents 'tweak if necessary
Range("H3:I24").ClearContents 'tweak if necessary

End Sub

If you have any further questions, just let me know!

 

[StumpedAgain]

Hi Tom,

I apologize, I forgot to account for the fact that you probably have =Sum()
formulas in the named ranges. The following pastes values only. Again, mind
the wrapping (hopefully I avoided bad wrapping)


Option Explicit
Sub TillSummary()

If Sheets("TillSummary").Range("A2") = "" Then

Range("Date").Copy
Sheets("TillSummary").Range("A2").PasteSpecial (xlPasteValues)
Range("MGRnow").Copy
Sheets("TillSummary").Range("B2").PasteSpecial (xlPasteValues)
Range("TillT1").Copy
Sheets("TillSummary").Range("C2").PasteSpecial (xlPasteValues)
Range("OvUn1").Copy
Sheets("TillSummary").Range("D2").PasteSpecial (xlPasteValues)
Range("TillT2").Copy
Sheets("TillSummary").Range("E2").PasteSpecial (xlPasteValues)
Range("OvUn2").Copy
Sheets("TillSummary").Range("F2").PasteSpecial (xlPasteValues)

Else:

Range("Date").Copy
Sheets("TillSummary").Range("A1").End(xlDown).Offset(1, 0) _
..PasteSpecial (xlPasteValues)
Range("MGRnow").Copy
Sheets("TillSummary").Range("B1").End(xlDown).Offset(1, 0) _
..PasteSpecial (xlPasteValues)
Range("TillT1").Copy
Sheets("TillSummary").Range("C1").End(xlDown).Offset(1, 0) _
..PasteSpecial (xlPasteValues)
Range("OvUn1").Copy
Sheets("TillSummary").Range("D1").End(xlDown).Offset(1, 0) _
..PasteSpecial (xlPasteValues)
Range("TillT2").Copy
Sheets("TillSummary").Range("E1").End(xlDown).Offset(1, 0) _
..PasteSpecial (xlPasteValues)
Range("OvUn2").Copy
Sheets("TillSummary").Range("F1").End(xlDown).Offset(1, 0) _
..PasteSpecial (xlPasteValues)

End If

Range("C324").ClearContents 'tweak if necessary
Range("H3:I24").ClearContents 'tweak if necessary

End Sub

 

[Ballwin Tom]

Thank You, "Stumpy"! This was awesome and easy. Greatly appreciate you taking
the time and sharing your skill. I learned some cool tricks.

 

[Shawn]

Hi StumpedAgain,

I have a somewhat similar question regarding a macro in excel 2007/vista:

I have an excel file with 2 worksheets: 'Form' is a sheet where I would like
to enter data (using a simplified example, a person's name, height and
weight); the other sheet I would like to use as a summary sheet tracking the
results of each form.

I have tried to record a macro that copies 'Form 1' and then links the newly
created fields in 'Form (2)' that will store person 2's information into the
next row of my summary sheet. The macro creates 'Form (2)' fine, but does not
duplicate the cell referencing.

Do you know an easy way to get excel to link the cells for me instead of
manually going through and linking them?

Thanks for your help.