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.