Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

Create new worksheet within workbook

Looking for the proper syntax to add a new worksheet when you need determining the next available Sheet name ("Sheet(?))

Sheets("Sheet(????????)").Select
0
Karen Schaefer
Asked:
Karen Schaefer
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Worksheets.add

will do both; add a worksheet  and select it.

To get the name you can use

Worksheets.add
wsname= activesheet.name
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this....

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

Open in new window

1
 
Rgonzo1971Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Karen SchaeferAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Karen SchaeferAuthor Commented:
ok It does not like the set statement.
0
 
Saqib Husain, SyedEngineerCommented:
Sorry

set WSname = ActiveSheet
0
 
Karen SchaeferAuthor Commented:
thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now