I have the following pivot table code. I want to be able to change it from it running on all sheets int he workbook to run on just the worksheet I am working with. The Code I currently have is...
Sub CreateAPivotTable()
Dim shtSource As Worksheet
Dim rngSource As Range, rngDest As Range
Dim pvt As PivotTable
On Error GoTo ErrHandler
'this prevents the screen from updating while the macro is running and
'will make the code run faster
Application.ScreenUpdating = False
For Each shtSource In ActiveWorkbook.Worksheets
If shtSource.Name <> "Summary" Then
'Rather than have the pivot table use all rows in column A-N
'just use what has actually been used.
Set rngSource = shtSource.Range("A1").CurrentRegion
'This is where the pivot table will be placed
Set rngDest = shtSource.Range("E1")
'This creates a pivot table. So rather than having to refer to PivotTables("PivotTable14") like before you can just refer to pvt
Set pvt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSource, _
Version:=xlPivotTableVersion12).CreatePivotTable(TableDestination:=rngDest, DefaultVersion:=xlPivotTableVersion12)
pvt.AddDataField pvt.PivotFields("Item"), "Count of Serial Rcvd", xlCount
With pvt.PivotFields("Item")
.Orientation = xlRowField
.Position = 1
End With
'Formatting
pvt.TableStyle2 = "PivotStyleDark7"
With shtSource.Cells.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End If
Next shtSource
'Turns screen updating back on - this line is critical otherwise
'it will be turned off after the macro has finished.
Application.ScreenUpdating = True
Exit Sub
'Simple error handler in case something goes wrong
ErrHandler:
Application.ScreenUpdating = True
MsgBox "An error occurred: " & Err.Description, vbExclamation, "Error"
End Sub
What do I need to do in order to create a shortcut keys to run the code? How do I also change the code so that it only works on the current worksheet instead of creating pivot tables on all worksheets?
Thanks!!!!
Bookmarks