Adding Sheets

  • Thread starter Thread starter Treeskier
  • Start date Start date
T

Treeskier

Hi,
I'm trying to add about 40 more sheets. Right now I have been just
pointing and copying one at a time and then having to rename one at a time.
Isn't there a way to just add many sheets at once and rename them
progressively? Value 20, Value 21, Value 22 etc.???
 
You could use a macro:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim iCtr As Long

For iCtr = 22 To 41 Step 1
Set wks = Worksheets.Add(after:=Worksheets(Worksheets.Count))
On Error Resume Next
wks.Name = "Value " & Format(iCtr, "00")
If Err.Number <> 0 Then
MsgBox "please rename: " & wks.Name & " manually"
Err.Clear
End If
On Error GoTo 0
Next ictr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
I forgot to add that I've only seen Excel for about a few weeks and have no
idea! I'm flying by trial and error.
macro?
 
Hi

I think this might be one of the functions available with ASAP Atilities'
add-in. Have a look at www.asap-utilities.com
You'll just create a list of sheet names and it does the rest (if I remember
right!) There's tons of useful stuff in the add-in.
--
Andy.


Treeskier said:
I forgot to add that I've only seen Excel for about a few weeks and have no
idea! I'm flying by trial and error.
macro?
Click to expand...
 
Did you look at this link from David McRitchie?
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Open your workbook
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out--Tools|macro|macros...|Run

If you need to have this kind of functionality, then keep the macro.

If you don't need it, you can remove the general module that you created.

If you have trouble, here's another link that will show you how:
http://contextures.com/xlfaqMac.html#NoMacros
(From Debra Dalgleish's site)
 
Thanks Andy, I'll check that out as well!

Andy B said:
Hi

I think this might be one of the functions available with ASAP Atilities'
add-in. Have a look at www.asap-utilities.com
You'll just create a list of sheet names and it does the rest (if I remember
right!) There's tons of useful stuff in the add-in.
Click to expand...
 
Back
Top