Solved

Create new worksheet within workbook

Posted on 2016-07-25
8
32 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
Comment Utility
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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
Try this....

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

Open in new window

1
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
 

Author Comment

by:Karen Schaefer
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
Comment Utility
ok It does not like the set statement.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
Sorry

set WSname = ActiveSheet
0
 

Author Closing Comment

by:Karen Schaefer
Comment Utility
thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now