Solved

Create new worksheet within workbook

Posted on 2016-07-25
8
41 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

679 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