Make multiple new sheets at the same time?

D

davesearle

Is there any way to make multiple new spreadsheets at one time in Excel
I think its Office 97 or 98. I need to make one separate sheet for eac
day of the year and I dont fancy making 365 sheets one at a time
Cheers, Dav
 
C

Cutter

You group the sheets by doing this:
Right click on the sheet tab and then left click on Select All Sheets.

The sheets are now grouped and whatever action you do on the visible
sheet will be done on all sheets that are grouped.

Remember to ungroup when you want to do something to just one sheet.
 
D

davesearle

Cool, cheers. But is there any way to create new sheets all at the sam
time without having to individually put them in?

Because otherwise I worked out that if I do it continuously withou
stopping it will take me over an hour to insert 365 sheets into m
database
 
G

Gord Dibben

Dave

One method.....I'm sure there will be more.

Sub Add_Sheets()
For i = 365 To 1 Step -1
Worksheets.Add.Name = "Day " & i
Next
End Sub

Took about 10 seconds on my setup.

BTW.......You are looking for trouble with this workbook having 365 sheets.

What are you planning to do with the workbook? There are most likely better
arrangements for working with days of the year that don't involve multiple
sheets as you propose.

Gord Dibben Excel MVP
 
D

Don

You can set the number of sheets that Excel opens with in
Tools/Options/General Tab to a max of 255, open a new
sheet and then add additional sheets with the insert
function. I don't know what the maximum limit is on
sheets, but I'm sure someone will post that here, if there
is one.

Also someone may come along with a VBA code to do what
you're trying to do.

Don
 
D

Don

Here's one macro that'll do what your want....set the
number within it to meet your needs.

Sub AddSheets()

For Count = 1 To 101
Sheets.Add
Next Count

End Sub
 
D

davesearle

Well I am writing a database for my gym and I have made it so that
have a sign in sheet so that when you type in the membership number i
comes up with the members details and the price to charge, then ther
is a field that adds up the total money earned in a day (just usin
'SUM').

Ideally I only want this one sign in sheet and to just save that on
piece of data from each day onto another sheet named 'Review', but
cant figure out how to do this!

If you have any idea how to do that then let me know! I tried with th
365 sheets but my comp doesnt have enough memory for it (its an ol
computer
 
D

davesearle

OK hopefully this will work, these are some screenshots. Obviously a
the moment it is quite simple but once I know what to do it wil
expand!

Will the macro above work with this?
 
G

Gord Dibben

Dave

Assuming you have the members' ID and details in a table on a second sheet or
an out of the way spot on sheet1.

Sheet1 will be your sign-in sheet where you would use VLOOKUP to retrieve
details when you enter a member ID in a cell.

Then use one of Ron de Bruin's Copying Macros to move the data to a third
sheet named "Review"

http://www.rondebruin.nl/copy1.htm

NOTE: where Ron's code uses "Sheet2" you can change that to "Review"

Your daily data will be copied/moved to Review sheet where you can keep a
running total and details of each day.

This sheet can then be sorted, filtered etc.

Sounds complex but when you get into it, not that bad.

Gord
 
Top