Creating multiple sheets with a macro

I have run into an issue where i want to be able to create multiple
"Resource Plans" sheet with a macro but to have them created as needed
so to speak. I am trying to create a
macro that can be used to see if a sheet called "Resource Plans"
exists.  The once it is verified then create an additional sheet called
"Resource Plans-1". Also,  I want to it to only
add a planned sheet each time you run the macro but each time to go up
one count numerically within the name of the sheet (i.e. Resource
Plans-1, Resource Plans-2, etc.) I would like to be able to do this for
up to six new sheets.

On each new Resource Plan sheet I want it to copy
the format and the area I define of either the original "Resource
Plans" sheet or of the
previously created  Resource Plans sheet, but this part I can probably
figure out myself.

I am not sure if the best way to do this is with if statments or case
arguments or something else.  I figure that for the macro to be run
once and create a sheet named
 I also know that the second time through it would have to check to see
if "Resource Plans" and "Resource Plans-1" sheets exist to know to add
the next sheet. I hope their is a way to do this in a macro. What I
have been trying to do is not working. I appreciate any help I can get.

Sincerely,
Kyle

Here is my current code in case it helps.  I am not sure if I can do it
this way or not.  Please let me know if I can do it this way and if so
what changes/additions shoudl I make to the code to make it work.

Sub NewResPlanSh()
'
'

    Dim WS As Worksheet

   For Each WS In Workbooks(1).Worksheets
   Select Case WS.Name
     Case Is = "Resource Plans"
       Worksheets("Resource Plans").Activate
    Range("A1:M26").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
        False, Transpose:=False
    Range("J24").Select
    Columns("J:J").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 23.57
    Columns("B:B").ColumnWidth = 17.86
    Columns("J:J").ColumnWidth = 12
     Range("B12:H20").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C5:J9").Select
    Selection.ClearContents
    Columns("I:I").ColumnWidth = 12
    Columns("B:B").ColumnWidth = 21.57
    Range("D16").Select
    ActiveSheet.Name = "Resource Plans-1"
     Case Is = "Resource Plans-1"
       Worksheets("Resource Plans-1").Activate
    Range("A1:M26").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
        False, Transpose:=False
    Range("J24").Select
    Columns("J:J").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 23.57
    Columns("B:B").ColumnWidth = 17.86
    Columns("J:J").ColumnWidth = 12
     Range("B12:H20").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C5:J9").Select
    Selection.ClearContents
    Columns("I:I").ColumnWidth = 12
    Columns("B:B").ColumnWidth = 21.57
    Range("D16").Select
    ActiveSheet.Name = "Resource Plans-2"
     Case Is = "Resource Plans-2"
       Worksheets("Resource Plans-2").Activate
    Range("A1:M26").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
        False, Transpose:=False
    Range("J24").Select
    Columns("J:J").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 23.57
    Columns("B:B").ColumnWidth = 17.86
    Columns("J:J").ColumnWidth = 12
     Range("B12:H20").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C5:J9").Select
    Selection.ClearContents
    Columns("I:I").ColumnWidth = 12
    Columns("B:B").ColumnWidth = 21.57
    Range("D16").Select
    ActiveSheet.Name = "Resource Plans-3"
   End Select
 Next WS

End Sub

0
syrney170 (1)
1/11/2006 6:31:09 PM
excel 39879 articles. 2 followers. Follow

1 Replies
719 Views

Similar Articles

[PageSpeed] 39

Kyle,

This will help you with the sheet creation. To use it you would do something 
like this:

Sub Name3Sheets()
Dim lCounter as long
For lCounter = 1 to 3
    SafelyRenameSheet ActiveWorkbook.Sheets(lCounter), "ResourcePlans"
Next lCounter
End Sub

Sub SafelyRenameSheet(shToName As Worksheet, strName As String)
'renames a sheet either to strName or to strName with a suffix if the 
original exists

Dim lSuffix As Long
Dim shTest As Worksheet

On Error GoTo SafelyRenameSheet_Error
lSuffix = 0

With ActiveWorkbook

    On Error Resume Next
    Set shTest = .Sheets(strName)
    On Error GoTo SafelyRenameSheet_Error

    If shTest Is Nothing Then

        shToName.Name = strName
        On Error GoTo 0
        Exit Sub

    End If

    Do While Not shTest Is Nothing

        lSuffix = lSuffix + 1

        Set shTest = Nothing
        On Error Resume Next
        Set shTest = .Sheets(strName & CStr(lSuffix))
        On Error GoTo SafelyRenameSheet_Error

    Loop

    shToName.Name = strName & CStr(lSuffix)

End With

EndRoutine:
On Error GoTo 0
Exit Sub

SafelyRenameSheet_Error:
'Error handling code here
Resume EndRoutine
End Sub

-- 
Robin Hammond
www.enhanceddatasystems.com

"syrney170" <syrney170@gmail.com> wrote in message 
news:1137004269.546864.32470@g43g2000cwa.googlegroups.com...
>
> I have run into an issue where i want to be able to create multiple
> "Resource Plans" sheet with a macro but to have them created as needed
> so to speak. I am trying to create a
> macro that can be used to see if a sheet called "Resource Plans"
> exists.  The once it is verified then create an additional sheet called
> "Resource Plans-1". Also,  I want to it to only
> add a planned sheet each time you run the macro but each time to go up
> one count numerically within the name of the sheet (i.e. Resource
> Plans-1, Resource Plans-2, etc.) I would like to be able to do this for
> up to six new sheets.
>
> On each new Resource Plan sheet I want it to copy
> the format and the area I define of either the original "Resource
> Plans" sheet or of the
> previously created  Resource Plans sheet, but this part I can probably
> figure out myself.
>
> I am not sure if the best way to do this is with if statments or case
> arguments or something else.  I figure that for the macro to be run
> once and create a sheet named
> I also know that the second time through it would have to check to see
> if "Resource Plans" and "Resource Plans-1" sheets exist to know to add
> the next sheet. I hope their is a way to do this in a macro. What I
> have been trying to do is not working. I appreciate any help I can get.
>
> Sincerely,
> Kyle
>
> Here is my current code in case it helps.  I am not sure if I can do it
> this way or not.  Please let me know if I can do it this way and if so
> what changes/additions shoudl I make to the code to make it work.
>
> Sub NewResPlanSh()
> '
> '
>
>    Dim WS As Worksheet
>
>   For Each WS In Workbooks(1).Worksheets
>   Select Case WS.Name
>     Case Is = "Resource Plans"
>       Worksheets("Resource Plans").Activate
>    Range("A1:M26").Select
>    Selection.Copy
>    Sheets.Add After:=Sheets(Sheets.Count)
>    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:= _
>        False, Transpose:=False
>    Range("J24").Select
>    Columns("J:J").EntireColumn.AutoFit
>    Columns("B:B").ColumnWidth = 23.57
>    Columns("B:B").ColumnWidth = 17.86
>    Columns("J:J").ColumnWidth = 12
>     Range("B12:H20").Select
>    Application.CutCopyMode = False
>    Selection.ClearContents
>    Range("C5:J9").Select
>    Selection.ClearContents
>    Columns("I:I").ColumnWidth = 12
>    Columns("B:B").ColumnWidth = 21.57
>    Range("D16").Select
>    ActiveSheet.Name = "Resource Plans-1"
>     Case Is = "Resource Plans-1"
>       Worksheets("Resource Plans-1").Activate
>    Range("A1:M26").Select
>    Selection.Copy
>    Sheets.Add After:=Sheets(Sheets.Count)
>    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:= _
>        False, Transpose:=False
>    Range("J24").Select
>    Columns("J:J").EntireColumn.AutoFit
>    Columns("B:B").ColumnWidth = 23.57
>    Columns("B:B").ColumnWidth = 17.86
>    Columns("J:J").ColumnWidth = 12
>     Range("B12:H20").Select
>    Application.CutCopyMode = False
>    Selection.ClearContents
>    Range("C5:J9").Select
>    Selection.ClearContents
>    Columns("I:I").ColumnWidth = 12
>    Columns("B:B").ColumnWidth = 21.57
>    Range("D16").Select
>    ActiveSheet.Name = "Resource Plans-2"
>     Case Is = "Resource Plans-2"
>       Worksheets("Resource Plans-2").Activate
>    Range("A1:M26").Select
>    Selection.Copy
>    Sheets.Add After:=Sheets(Sheets.Count)
>    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:= _
>        False, Transpose:=False
>    Range("J24").Select
>    Columns("J:J").EntireColumn.AutoFit
>    Columns("B:B").ColumnWidth = 23.57
>    Columns("B:B").ColumnWidth = 17.86
>    Columns("J:J").ColumnWidth = 12
>     Range("B12:H20").Select
>    Application.CutCopyMode = False
>    Selection.ClearContents
>    Range("C5:J9").Select
>    Selection.ClearContents
>    Columns("I:I").ColumnWidth = 12
>    Columns("B:B").ColumnWidth = 21.57
>    Range("D16").Select
>    ActiveSheet.Name = "Resource Plans-3"
>   End Select
> Next WS
>
> End Sub
> 


0
rjNOrhSPAM (52)
1/13/2006 1:05:05 AM
Reply:

Similar Artilces:

Killer macro idea for business mailings
One of you programmers should write a macro to validate a city from zip code. I receive excel files from response cards and a lot of people will put some random letters for their city instead of the actual city name, or maybe they'll put the wrong abrieviation for their state. It would be awesome if all the zipcodes could validate the state field and change if necessary and the same for the city fields too. Okay, have at it :) Okay, well good idea I think anyways, but it turns out that my columns just weren't wide enough so it didn't display more then 2 letters. ...

How do I create a sample in Excel?
I am trying to create a sample, needing every 20th record out of around 3000 records. Mary, You need a VBA procedure. Sub AAA() Dim RowNdx As Long Dim DestRng As Range Set DestRng = Worksheets("Sheet2").Range("A1") For RowNdx = 1 To 3000 Step 20 Rows(RowNdx).Copy Destination:=DestRng Set DestRng = DestRng(2, 1) Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mary" <Mary@discussions.microsoft.com> wrote in message news:ADE0AD7F-B459-41AE-8B2...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

multiple email identities
HELP! My husband and I share the same computer and we have our email come to our inbox through Microsoft Outlook. However it is merging our accounts- how do we seperate into 2 identities so we each get our own mail! It is so frustrating! Please help! Thanks Kat Outlook does not use Identities - it uses profiles. See this page for more information: http://www.slipstick.com/outlook/share.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After s...

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

How do I create a click on + symbol to open a root and click on -.
I'm looking to create an excel file with drop down menus. I'd like to have a category. Click on the "+" symbol and the category opens up and shows all of the subcategories. Each category can further be opened if I so choose. Each category can be have a number total associated with it. When you click the "-" symbol. The subcategories close and the sum total of all subcategories is shown in the category total. example. creating a budget. Category is utilities sub categories are: phone, cable, electric, gas, etc... Monthly utility total ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

creating a spredsheet and log the info into another spredsheet
hello, I have a excel sheet that is printed out and a cashier manual enters information, invoice number, invoice amount, cash amount, check amount, amex amount.... I would like to have the cashier input this information on her PC and print a copy to go along with the deposit and at the same time log the information into a google excel document I created. Any ideas how this can be done? ...

Macros not performing correctly
I recorded a macro to place a vlookup formula in four cells that are side by side. When I run the macro it only performs the first task, it does not go on to do the other three. I used the tab key to go to the next cell while recording the macro, I don't know if that's why it's not working or if it's something else. The script looks like this: Sub EERates() ' ' EERates Macro ' Macro recorded 3/4/2005 by BCBSOK ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)" Range("F15").Select ActiveCell.FormulaR1C...

Creating Exchange 2003 organization
Is it possible to have two Exchange Organizations in the same domain? I need to reproduce a problem and was hoping to install an Exchange server to an existing domain however I need the organization to be different. Is this possible? When I installed EX 2003 I wasn't prompted for information other than location of files. Thanks in advance On Mon, 28 Feb 2005 08:09:03 -0800, "RP" <RP@discussions.microsoft.com> wrote: >Is it possible to have two Exchange Organizations in the same domain? I need >to reproduce a problem and was hoping to install an Exchange server...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

how can I do a lookup into another sheet?
how can I do a lookup into another sheet? thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like =VLOOKUP(123,Sheet2!A1:B10,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "melawaisi" <melawaisi.ws67m@excelforum-nospam.com> wrote in message news:melawaisi.ws67m@excelforum-nospam.com... > > how can I do a lookup into another sheet? > thanks > > > ------...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

Can't create the item #2
I use Outlook from MS Office 2003 Business Edition. Everything has worked fine until the past few days. Now, whenever I click on an E-mail address link inside a webpage, I get a message from Outlook that says "Can't create the item." One person on this group gave me the following advice: >Close Outlook, find and rename the frmscache.dat to .old I did searches on all my drives and the file named above was not found. I doubled checked the search to confirm that it included hidden files and system folders in the search. Still no luck. More thoughts? ...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...

calendar for multiple time zone users: all day events
Users in different time zones post absences and meetings to a shared company calendar. When an all day event is scheduled in one time zone, it shows as spanning two days for other time zone users. How do we make this work properly? For the others it does intrude on a second day. BossLady wrote: > Users in different time zones post absences and meetings to a shared company > calendar. When an all day event is scheduled in one time zone, it shows as > spanning two days for other time zone users. How do we make this work > properly? Until you upgrade to Outlook...

How do I create upper/lower case letters in cells?
I have a large spreadsheet with names/addresses that are all capitalized. I want to make them upper and lower case (SMITH = Smith). What's the formula? You could create helper cells with this formula =proper(A1) "boz130" wrote: > I have a large spreadsheet with names/addresses that are all capitalized. I > want to make them upper and lower case (SMITH = Smith). What's the formula? ...

Seperate jobs for multiple worksheets
Separate jobs are sent to printers when I select multiple worksheets... If I print to PDF, multiple files are created. How can I avoid this? ...