Solved

Create new worksheet within workbook

Posted on 2016-07-25
8
40 Views
Last Modified: 2016-07-25
Looking for the proper syntax to add a new worksheet when you need determining the next available Sheet name ("Sheet(?))

Sheets("Sheet(????????)").Select
0
Comment
Question by:Karen Schaefer
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41727948
Worksheets.add

will do both; add a worksheet  and select it.

To get the name you can use

Worksheets.add
wsname= activesheet.name
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41727953
Try this....

Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Sheet" & Sheets.Count + 1

Open in new window

1
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41727969
Hi,

pls try

Sub macro()
intMax = 0
For Each sh In ActiveWorkbook.Sheets
    If sh.Name Like "Sheet#*" Then
        intMax = WorksheetFunction.Max(intMax, Right(sh.Name, Len(sh.Name) - 5))
    End If
Next
Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Sheet" & intMax + 1
End Sub

Open in new window

Regards
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Karen Schaefer
ID: 41728339
How do I use the wsname to rename the worksheet to "by State"
Dim WSname As Worksheet

    Sheets("BillingFinal").Select
    Worksheets.Add
    WSname = ActiveSheet.name
    WSname.Select
    WSname.name = "By State"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("ThisWorkbookDataModel"), Version:=6). _
        CreatePivotTable TableDestination:="By State!R1C1", TableName:= _
        "PivotTable1", DefaultVersion:=6
    Cells(1, 1).Select
    Sheets("By State").Select

Open in new window

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41728344
Dim WSname As Worksheet

    Sheets("BillingFinal").Select
    Worksheets.Add
    set WSname = ActiveSheet.name
    WSname.Select
    WSname.name = "By State"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("ThisWorkbookDataModel"), Version:=6). _
        CreatePivotTable TableDestination:="By State!R1C1", TableName:= _
        "PivotTable1", DefaultVersion:=6
    Cells(1, 1).Select
    Sheets("By State").Select
0
 

Author Comment

by:Karen Schaefer
ID: 41728355
ok It does not like the set statement.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 41728428
Sorry

set WSname = ActiveSheet
0
 

Author Closing Comment

by:Karen Schaefer
ID: 41728509
thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question