Solved

Create new worksheet within workbook

Posted on 2016-07-25
8
43 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 31

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 52

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

628 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