is it possible to have macro to auto name worksheet

wally32

New Member
Joined
Mar 7, 2011
Messages
29
hi

not sure whether this is possible. i have a macro which automatically runs a pivottable. the macro is run on a once month basis. the pivottable that is produced comes on a new worksheet, which is named Sheet 1 or 2 or 3, by the system etc. depending on what month it is i.e. sheet 1 would be analysis jan, sheet 2 analysis feb and so on.
what i would like to do is when the new pivottable worksheet is produced is to automatically name it depending what month it is eg. analysis jan,

can someone please advise if this is possible

thanks
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,684
Office Version
  1. 365
Platform
  1. Windows
Should be possible, can you post your code please?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps like this

Code:
ActiveSheet.Name = "Analysis " & Format(Date, "mmmm")
 

wally32

New Member
Joined
Mar 7, 2011
Messages
29
thanks for the swift response.the code for the pivot table macro is as follows
Option Explicit
Sub Testcreatepivottable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Range("B1:C1").CurrentRegion).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet
.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Score"), "Count of Score", xlCount
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Appellant")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Prop no.")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Score")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleMedium2"

End Sub
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,684
Office Version
  1. 365
Platform
  1. Windows
Just add Peter's code to the end of what you posted and it should be fine! (before the End Sub line)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,984
Messages
5,526,062
Members
409,684
Latest member
Nazmul00

This Week's Hot Topics

Top