cierrasmadre
New Member
- Joined
- Jun 14, 2017
- Messages
- 9
Keep in mind I'm self taught so please excuse any ignorance of mine. Background: Daily, I receive a report that consists of 20-25 text files, 1 for each state we have business in. I have a process that consists of a few different scripts run one after the other. It does a lot to the files with an end result of 1 workbook with 3 tabs showing specific segments of the information along with 3 tabs for pivot tables for each segment of data, followed by 20-25 tabs for breaking out some remaining data per state. My process does quite a bit and I'm aware it's DEFINITELY not the cleanest way, but it works and usually takes a minute vs 45 minutes or more manually.
Until today that is. I know where the issue is in my script and I know how to fix it, but not HOW to fix it. I'm hoping someone can help me in updating this code.
This part of my process pulls a 2nd pivot table from my main set of data (tab labeled as "All Records") to it's own tab, then copies the table and pastes it onto my main stats tab, labeled as "Stats - All Records". I do this because I couldn't figure out how to get it to create directly on my main stats tab. It then looks for 2 sets of data on this 2nd pivot table and pulls the raw data (manually we would double click the total next to the data to create a new tab of the raw data). Then renames each of the 2 new tabs accordingly and move them to the order I need in the workbook.
The problem is that today's report does not include any records for the 2nd set of data (labeled as Hold Days 10). So I need the script to say If not found, then End and continue on with the process. I'm attaching this entire script, but it's the last section I'm having issues with (unless someone knows how to clean the entire thing up ). After it runs this one, the process moves on to run my next script.
Until today that is. I know where the issue is in my script and I know how to fix it, but not HOW to fix it. I'm hoping someone can help me in updating this code.
This part of my process pulls a 2nd pivot table from my main set of data (tab labeled as "All Records") to it's own tab, then copies the table and pastes it onto my main stats tab, labeled as "Stats - All Records". I do this because I couldn't figure out how to get it to create directly on my main stats tab. It then looks for 2 sets of data on this 2nd pivot table and pulls the raw data (manually we would double click the total next to the data to create a new tab of the raw data). Then renames each of the 2 new tabs accordingly and move them to the order I need in the workbook.
The problem is that today's report does not include any records for the 2nd set of data (labeled as Hold Days 10). So I need the script to say If not found, then End and continue on with the process. I'm attaching this entire script, but it's the last section I'm having issues with (unless someone knows how to clean the entire thing up ). After it runs this one, the process moves on to run my next script.
Code:
Sub I_InsertPivotTable2()
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
'Insert a New Blank Worksheet
On Error Resume Next
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Stats - Error Message"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Stats - Error Message")
Set DSheet = Worksheets("All Records")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="StatsPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="StatsPivotTable")
'Insert Row Fields
With ActiveSheet.PivotTables("StatsPivotTable").PivotFields("Error Message")
.Orientation = xlRowField
.Position = 1
End With
'Insert Data Field
ActiveSheet.PivotTables("StatsPivotTable").AddDataField ActiveSheet.PivotTables _
("StatsPivotTable").PivotFields("KEY"), "Count of Key", xlSum
With ActiveSheet.PivotTables("StatsPivotTable").PivotFields("Count of Key")
.Caption = "Count of Key"
.Function = xlCount
End With
'Format Pivot Table
ActiveSheet.PivotTables("StatsPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("StatsPivotTable").TableStyle2 = "PivotStyleMedium9"
Columns("B:C").Select
Selection.Copy
Sheets("Stats - All Records").Activate
Columns("H:I").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets("Stats - Error Message").Delete
Application.DisplayAlerts = True
' Locate records on Stats tab containing Record Missing and create a new tab with the raw data
Worksheets("Stats - All Records").Activate
Cells.Find(What:="RECORD MISSING", after:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
Selection.ShowDetail = True
ActiveSheet.Name = "Demo Master Missing"
Worksheets("Demo Master Missing").Move _
after:=Worksheets("All Records")
' Locate records on Stats tab containing Hold Days 10 and create a new tab with the raw data
Worksheets("Stats - All Records").Activate
Cells.Find(What:="Hold Days 10", after:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
Selection.ShowDetail = True
ActiveSheet.Name = "Hold Days 10"
Worksheets("Hold Days 10").Move _
after:=Worksheets("Demo Master Missing")
End Sub