Need some help with this code

[dwhite30518]

I have the following code...

Sub CreateAPivotTable()

Dim shtSource As Worksheet
Dim rngSource As Range
Dim 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


Set shtSource = ActiveSheet

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("Serial Rcvd"), "Count of Serial Rcvd", xlCount
pvt.AddDataField pvt.PivotFields("Serial Shipped"), "Count of Serial Shipped", xlCount
pvt.PivotFields("Count of Serial Rcvd").Caption = " Serial Rcvd"
pvt.PivotFields("Count of Serial Shipped").Caption = " Serial Shipped"

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
Else
MsgBox ("Please remove existing sheet with the name of Summary")

End If

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


It works great on the current worksheet it is tied to, but how do I apply this code to an identical worksheet, that needs the same pivot tables created? I want to me able to basically..."Copy & Paste" this code to any workbook of similar data. HELP?!?!?!?!!?!?!?!?!

[protonLeah]

Forum Rules
Please read and adhere to these simple rules!

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing.
Select/hi-lite your code and click the [#] button at the top of the editor window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the edit window:

[code]
your code here ...
and here ...
and here
[/code]

... and appear like this when posted:

Please Login or Register  to view this content.

You can also type the code tags in manually if you prefer.