auto worksheet naming

mikemathis

Board Regular
Joined
Jul 9, 2003
Messages
135
Office Version
  1. 2019
Platform
  1. Windows
Ok, here's what I'm doing and hoping that there's an automated way to do it. I'm creating a donation sheet (a summary sheet followed by individual worksheets for each giving family). I have the summary sheet with the family names in column "A" (ascending order). My puzzle is, what happens when I have a 'new' giving family through the year? I have referenced the summary rows (for a single family) onto it's own unique worksheet to show a detail of what that family has given (which will be printed out on a quarterly basis).

I'm not a 'coder' and have no macros used in this sheet (which currently has over 65 worksheets to it).

My questions are:
1. Is it possible to dynamically name a worksheet from a cell in my summary worksheet?

2. Is there a way to have a single template detail sheet (since the giving statements will be given out quarterly and have no other purpose) and have a *pick a 'family', print, next family*


many thanks.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
If I could get an answer to #1, that would be wonderful.

Can you name a worksheet (an individual tab) via formula? I think that's what I want to ask.

thanks for any help.

Mike
 
Upvote 0
#1

Something to start with:
Assumes new family is added to bottom of list in col A
Place this in a normal module and attach to a command button.


Sub newFamily()
Dim nm

'find last entry in column a (new family name)
nm = Sheets("sheet1").Range("a65536").End(xlUp).Address

On Error Resume Next
Sheets(nm).Select
If ActiveSheet.Name = nm Then
MsgBox "Sheet for " & nm & " already exists, choose a unique name"
Exit Sub
Else
Sheets.Add Before:=Worksheets(Worksheets.Count)
' name new sheet
Application.ActiveSheet.Name = nm
'// possibly sort your summary sheet to keep family names alpha
End Sub
 
Upvote 0
Thanks Bob.

I following (or at least think I'm following) how you're getting the worksheet name. I'll give it a try tonight and see how it works!

My first thought was like a reverse INDIRECT using the list of people in Column "A" on the Summary sheet... but if this works... then yippee!!!!


Mike
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top