Hi,
Should be a quick fix.
I have code to do the following:
- Up date to a pivot table (worksheet select action)
- Copy to multiple sheets (Module)
The two part work in isolation, but crash when put together.
Code is as follows:
Module
Code
Sub ModuleFull()Code
Application.ScreenUpdating = False
Call Deletesheets
Call Generate_CC_Reports_loop
End Sub
Sub Deletesheets()
'To delete sheets out of report
Dim Col As Integer
Application.ScreenUpdating = False
For i = Sheets.Count - 1 To 2 Step -1
'see if the sheet name contains a pipe symbol, if yes do nothing, if yes delete the sheet
If InStr(Sheets(i).Name, "|") > 0 Then
'delete the sheet
Application.DisplayAlerts = False
Sheets(i).Delete
Application.DisplayAlerts = True
End If
Next
Sheets("CCREP").Select
End Sub
Sub Generate_CC_Reports_loop()
Application.ScreenUpdating = False
For Row = 1 To 50
'copy over the cc (cell value)
Sheets("CCREP").Range("A8").Value = Sheets("CCList").Cells(Row, 1).Value
'To allow pivot to update before copyingto another sheet
ActiveWorkbook.RefreshAll
'Application.Wait "00:00:10"
'create the report for this cc
Call CopyReport
Next
End Sub
'Creates a copy of the sheet
Sub CopyReport()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets("CCREP")
ws.Copy After:=ws
With ActiveSheet
'name the sheet
.Name = ws.Range("A8").Value & " | " & Format(Now, "mm-ss")
.Unprotect
'turn formulas to values
'but only the UsedRange of a worksheet, this is, the cells that are actually in use
'it stops where you end up if you press Ctrl-End
.UsedRange.Cells.Value = .UsedRange.Cells.Value
End With
End Sub
Worksheet code
Worksheet
Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("K20:K21")) Is Nothing Then Exit Sub
'Set the Variables to be used
'Are assigned values and then used later
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
'Here you amend to suit your data
'Assigning the Pivot table fields to variables already declared
Set pt = Worksheets("CCREP").PivotTables("PivotTable1")
Set Field1 = pt.PivotFields("Cost Centre")
Set Field2 = pt.PivotFields("Period")
'Linking values inputted in the front end to variables
NewCat1 = Worksheets("CCREP").Range("K20").Value
NewCat2 = Worksheets("CCREP").Range("K21").Value
'This updates and refreshes the PIVOT table
'With Loop
With pt
Field1.ClearAllFilters
'Assigns the newcat1 variable(user defined)to the pivot filter
Field1.CurrentPage = NewCat1
Field2.ClearAllFilters
Field2.CurrentPage = NewCat2
pt.RefreshTable
End With
End Sub[U][B]
[/B][/U]I have also uploaded the sample work book with the code in it.
Basis of how design should work
- Run 'Create' Creat macro button
- Module VBA cycles through cost centre list and updates cell A8 on Sheet "CCREC"
- This inturn links to the pivot table cells K20:k21
- The VBA is the module refreshes the sheet so new pivot table values are returned
- The vba then creates copies past values copies of each pivot table created.
I would prefer that the code I am using be corrected and explanation be added to the code so I can later work it out for my self.
Thanks
Matt