Questions: Copy ranges, autofilter macro, subtotals, & naming shee

[Guest]

I have a workbook for receipt tracking for a theatre season. Each sheet
contains all the info about one show, and there will be as many as 7 shows in
a season. Each show has receipts from several departments (props, wardrobe,
lighting, etc.) I've set up each sheet like this:

Row 4: Props Wardrobe
Row 5: Date Store Amt VisaY/N? Name Date Store Amt VisaY/N? Name

....etc moving to the right. The data begins in row 6

Question 1: I want to rename the sheet tabs based on the text entered in
merged cells A1:C1 (show title) and I'd like this to happen automatically
once the data is entered in the cell (Currently it says "<Enter Show 1 Name
Here>")

Question 2: I want to be able to pull all the records from each sheet and
category for which the user has entered "Y" under the Visa column and copy
that information to a new sheet named "Visa". My difficulty is that I can't
pull the entire row; I need to just pull the row under a particular
department heading.

I've created a macro that will copy each range I need from each sheet, then
autofilter for "Y" and sort by date. I'm wondering if there's an easier way
to do this. Here's part of the code:

Sheets("Visa").Select
Range("B35").Select
Sheets("Show 1").Select
Range("F6:J55").Select
Selection.Copy
Sheets("Visa").Select
ActiveSheet.Paste

Range("B85").Select
Sheets("Show 1").Select
Range("N6:R55").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Visa").Select
ActiveSheet.Paste

Range("B135").Select
Sheets("Show 1").Select
Range("V6:Z55").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Visa").Select
ActiveSheet.Paste

....and on and on and on (it ends up going up to row 3150)
I fear that this code will be useless if I change the names of the sheets
too. Sigh. I'm very new to macros (a week!) so I'm sure I've screwed up
somewhere.

Question 3: I also would like to be able to subtotal all records in "Visa"
by month, but when I try to subtotal it does it for each change in "Date"
(i.e., subtotals Jan 8 and 9, rather than both together).

I've already learned a lot from reading these forums, but I don't understand
macros enough to modify the code I've found here that might work. Any help
you could provide would be much appreciated! My apologies for the long post,
too.

 

[Dave Peterson]

I've always found it easier when all my data is on one location.

I'd add one more column (a new column A) and put in the show names. Then I
could apply data|filter|Autofilter to the range and show whatever show I wanted
(or all of them).

In fact, I'd add a new column B to be used for the type of charge (Props,
Wardrobe, etc) and have one charge per row.

I like to put my titles in row 2 and my subtotals in row 1 (so they're always
visible).

Then when I can apply filtering to show just the wardrobe charges for any or all
shows.

My subtotal formulas would look like:
=subtotal(3,a3:a9999)
To count the number of items showing in column A

=subtotal(9,e3:e9999)
to sum the amounts

By having the data laid out like this, you can do some nice filtering and even
pivottables to get summaries (and group by month per show per category) (and
pretty charts, too!).

Just a thought.

If you may want to invest a little time in learning about pivottables.

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Guest]

I've always found it easier when all my data is on one location.

Yeah, if this was for me, I'd do it completely differently! But I'm
setting it up for a friend who wants to have each show on its own sheet.
She'll also use a new workbook for each season.

I like to put my titles in row 2 and my subtotals in row 1 (so they're always
visible).

Excellent idea. Thank you. What I've done is put the running totals in
columns A to C and used the freeze panes function so as you scroll to the
right, you can still see them.

By having the data laid out like this, you can do some nice filtering and even
pivottables to get summaries (and group by month per show per category) (and
pretty charts, too!).

I've got another sheet that's a pivot table, but I've had to type "='Show
1'K8" (the cell where I've summed everything already) for each one. It does
it automatically, but took a bit of time to create. And yeah, got charts too


Thanks for the links. I haven't used pivot tables much.