Optimizing a Macro

M

malonex3

New Member
Joined
Mar 31, 2011
Messages
8
Hello all, sorry in advance, but this post is bound to be really long.

I am by no means a VBA coder, but I have managed to record a few macros that have reduced the time to produce some weekly reports from about 2 hours down to less than 10 minutes, and that includes the time needed to download the raw data.

The macro below is the largest one, but I know it is full of redundant code that could be reduced. I am hoping someone has the patience to look this over and provide some insight on how I can optimize the code to perform better.

I think I've been able to boil down what I hope to achieve to three basic requirements:

1. I would like to create a sheet for each unique value in the "Configuration Name" column. Right now I use a For, Next loop to create five sheets, which is the maximum number of unique values that would appear. This report is ran weekly so not all locations will appear every week.


2. I would like rename each new sheet to match the unique values from step 1.


3. For each location there is a section of code that copies the data for that location into the sheet that matches the location's name, produces a pivot table on a new sheet which is renamed accordingly, finally the worksheet and pivot table for each location is moved to a new workbook which is saved with the location's name and closed. I would like to reduce that function to a loop, again based on the unique values produced in step 1, to eliminate the redundant coding.

Unfortunately I can't post attachments, or screenshots, but if anyone would like to look at the file I'm trying to work with please contact me. thank you.

Code: 
 Sub Hosts_With_Vulnerabilities()
'
' Hosts_With_Vulnerabilities Macro
' Formats the Devices with Vulnerabilities report from the weekly EVAN Scans.
'
'
    Rows("1:10").Select
    Range("A10").Activate
    Selection.Delete Shift:=xlUp
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A2").Select
    ActiveWorkbook.Names("Print_Titles").Delete
    Sheets("Fix - Hosts with Vulnerabilitie").Name = "Master_Data"
        ActiveWorkbook.Names.Add Name:="MasterData", RefersToR1C1:= _
        "=OFFSET('Master_Data'!R1C1,0,0,COUNTA('Master_Data'!C),COUNTA('Master_Data'!R[-1]))"
    ActiveWorkbook.Names("MasterData").Comment = ""
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "MasterData", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Configuration Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Host Type")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("NBName")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Rating"), "Count of Rating", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Rating")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Focus", xlDataAndLabel, _
        True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Rating").PivotItems("Focus" _
        ).Position = 1
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "High", xlDataAndLabel, True
    With Selection.Font
        .Color = -16727809
        .TintAndShade = 0
    End With
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("NBName").AutoSort _
        xlDescending, "Count of Rating"
    Range("A5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Configuration Name"). _
        AutoSort xlDescending, "Count of Rating"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Configuration Name"). _
        ShowDetail = False
    ActiveSheet.PivotTables("PivotTable1").Name = "MasterPivot"
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "MasterPivot"
'
'   Create and copy data to each base's worksheet
'
    On Error Resume Next
    Sheets("Master_Data").Select
    Selection.AutoFilter
    Range("A2").Select
'
'   Inserts blank worksheets
    Dim s As Integer
    For s = 1 To 5
    Sheets.Add After:=ActiveSheet
    Next s
'
' Renames blank sheets and formats columns widths
    Sheets("Sheet2").Name = "Location1"
    Sheets("Sheet3").Name = "Location2"
    Sheets("Sheet4").Name = "Location3"
    Sheets("Sheet5").Name = "Location4"
    Sheets("Sheet6").Name = "Location5"
    Sheets("Location1").Activate
    Sheets(Array("Location1", "Location2", "Location3", "Location4", "Location5")).Select
    Selection.ColumnWidth = 10
    Columns("B:B").Select
    Selection.ColumnWidth = 10
    Columns("C:C").Select
    Selection.ColumnWidth = 18
    Columns("D:D").Select
    Selection.ColumnWidth = 34
    Columns("E:E").Select
    Selection.ColumnWidth = 24
    Columns("F:F").Select
    Selection.ColumnWidth = 24
    Columns("G:G").Select
    Selection.ColumnWidth = 11
    Columns("H:H").Select
    Selection.ColumnWidth = 14
    Columns("I:I").Select
    Selection.ColumnWidth = 15
    Columns("J:J").Select
    Selection.ColumnWidth = 13
    Columns("K:K").Select
    Selection.ColumnWidth = 23
    Columns("L:L").Select
    Selection.ColumnWidth = 36
    Columns("M:M").Select
    Selection.ColumnWidth = 16
    Columns("N:N").Select
    Selection.ColumnWidth = 13
    Columns("O:O").Select
    Selection.ColumnWidth = 5
    Columns("P:P").Select
    Selection.ColumnWidth = 5
    Columns("Q:Q").Select
    Selection.ColumnWidth = 8
    Columns("R:R").Select
    Selection.ColumnWidth = 22
    Columns("S:S").Select
    Selection.ColumnWidth = 27
    Columns("T:T").Select
    Selection.ColumnWidth = 38
    Columns("U:U").Select
    Selection.ColumnWidth = 19
    Columns("V:V").Select
    Selection.ColumnWidth = 50
    Columns("W:W").Select
    Selection.ColumnWidth = 10
    Columns("X:X").Select
    Selection.ColumnWidth = 10
    Columns("Y:Y").Select
    Selection.ColumnWidth = 50
    Range("A1").Select
'
' Copy Location1's Data to Location1 worksheet
'
    Sheets("Master_Data").Select
    Range("A2").Select
    ActiveSheet.Range("A:Y").AutoFilter Field:=4, Criteria1:= _
        "Location1"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Location1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2").Select
    ActiveWindow.FreezePanes = True
'
' Create Location1's Pivot Table
'
    Sheets("Location1").Select
    ActiveWorkbook.Names.Add Name:="Location1Data", RefersToR1C1:= _
        "=OFFSET(Location1!R1C1,0,0,COUNTA(Location1!C),COUNTA(Location1!R))"
    ActiveWorkbook.Names("Location1Data").Comment = ""
    Sheets.Add
    Sheets("Sheet7").Select
    Sheets("Sheet7").Name = "Location1_Pivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Location1Data", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Location1_Pivot!R3C1", TableName:="Location1Table", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Location1_Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Location1Table").PivotFields("Host Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Location1Table").PivotFields("NBName")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Location1Table").PivotFields("Vulnerability Name")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("Location1Table").PivotFields("IP Address")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("Location1Table").AddDataField ActiveSheet.PivotTables( _
        "Location1Table").PivotFields("Rating"), "Count of Rating", xlCount
    With ActiveSheet.PivotTables("Location1Table").PivotFields("Rating")
        .Orientation = xlColumnField
        .Position = 1
    End With
On Error Resume Next
    ActiveSheet.PivotTables("Location1Table").PivotSelect "Focus", xlDataAndLabel, _
        True
'    On Error Resume Next
    ActiveSheet.PivotTables("Location1Table").PivotFields("Rating").PivotItems("Focus" _
        ).Position = 1
'    On Error Resume Next
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
'    On Error Resume Next
    ActiveSheet.PivotTables("Location1Table").PivotSelect "High", xlDataAndLabel, True
'    On Error Resume Next
    With Selection.Font
        .Color = -16727809
        .TintAndShade = 0
    End With
    Range("A5").Select
    ActiveSheet.PivotTables("Location1Table").PivotFields("Host Type").AutoSort _
        xlDescending, "Count of Rating"
    ActiveSheet.PivotTables("Location1Table").PivotFields("Host Type").ShowDetail = _
        False
    ActiveSheet.PivotTables("Location1Table").ShowTableStyleRowStripes = True
'
' Move Location1's worksheets to new Workbook
'
    Sheets(Array("Location1_Pivot", "Location1")).Select
    Sheets("Location1").Activate
    Sheets(Array("Location1_Pivot", "Location1")).Move
    Sheets("Location1").Activate
    Range("A2").Select
    Sheets("Location1_Pivot").Activate
    Range("A5").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\e305779\Downloads\EVAN Scans Working Folder\Hosts with Vulnerabilities Location1.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
'
' Copy Location2's Data to Location2 worksheet
'
    Sheets("Master_Data").Select
    Range("A2").Select
    ActiveSheet.Range("A:Y").AutoFilter Field:=4, Criteria1:= _
        "Location2"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Location2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2").Select
    ActiveWindow.FreezePanes = True


' Create Location2's Pivot Table
'
    Sheets("Location2").Select
    ActiveWorkbook.Names.Add Name:="Location2Data", RefersToR1C1:= _
        "=OFFSET(Location2!R1C1,0,0,COUNTA(Location2!C),COUNTA(Location2!R))"
    ActiveWorkbook.Names("Location2Data").Comment = ""
    Sheets.Add
    Sheets("Sheet8").Select
    Sheets("Sheet8").Name = "Location2_Pivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Location2Data", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Location2_Pivot!R3C1", TableName:="Location2Table", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Location2_Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Location2Table").PivotFields("Host Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Location2Table").PivotFields("NBName")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Location2Table").PivotFields("Vulnerability Name")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("Location2Table").PivotFields("IP Address")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("Location2Table").AddDataField ActiveSheet.PivotTables( _
        "Location2Table").PivotFields("Rating"), "Count of Rating", xlCount
    With ActiveSheet.PivotTables("Location2Table").PivotFields("Rating")
        .Orientation = xlColumnField
        .Position = 1
    End With
On Error Resume Next
    ActiveSheet.PivotTables("Location2Table").PivotSelect "Focus", xlDataAndLabel, _
        True
'    On Error Resume Next
    ActiveSheet.PivotTables("Location2Table").PivotFields("Rating").PivotItems("Focus" _
        ).Position = 1
'    On Error Resume Next
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
'    On Error Resume Next
    ActiveSheet.PivotTables("Location2Table").PivotSelect "High", xlDataAndLabel, True
'    On Error Resume Next
    With Selection.Font
        .Color = -16727809
        .TintAndShade = 0
    End With
    Range("A5").Select
    ActiveSheet.PivotTables("Location2Table").PivotFields("Host Type").AutoSort _
        xlDescending, "Count of Rating"
    ActiveSheet.PivotTables("Location2Table").PivotFields("Host Type").ShowDetail = _
        False
    ActiveSheet.PivotTables("Location2Table").ShowTableStyleRowStripes = True
'
' Move Location2's worksheets to new Workbook
'
    Sheets(Array("Location2_Pivot", "Location2")).Select
    Sheets("Location2").Activate
    Sheets(Array("Location2_Pivot", "Location2")).Move
    Sheets("Location2").Activate
    Range("A2").Select
    Sheets("Location2_Pivot").Activate
    Range("A5").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\e305779\Downloads\EVAN Scans Working Folder\Hosts with Vulnerabilities Location2.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
'
' Copy Location3's Data to Location3 worksheet
'
    Sheets("Master_Data").Select
    Range("A2").Select
    ActiveSheet.Range("A:Y").AutoFilter Field:=4, Criteria1:= _
        "Location3"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Location3").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2").Select
    ActiveWindow.FreezePanes = True
'
' Create Location3's Pivot Table
'
    Sheets("Location3").Select
    ActiveWorkbook.Names.Add Name:="Location3Data", RefersToR1C1:= _
        "=OFFSET(Location3!R1C1,0,0,COUNTA(Location3!C),COUNTA(Location3!R))"
    ActiveWorkbook.Names("Location3Data").Comment = ""
    Sheets.Add
    Sheets("Sheet9").Select
    Sheets("Sheet9").Name = "Location3_Pivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Location3Data", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Location3_Pivot!R3C1", TableName:="Location3Table", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Location3_Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Location3Table").PivotFields("Host Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Location3Table").PivotFields("NBName")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Location3Table").PivotFields("Vulnerability Name")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("Location3Table").PivotFields("IP Address")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("Location3Table").AddDataField ActiveSheet.PivotTables( _
        "Location3Table").PivotFields("Rating"), "Count of Rating", xlCount
    With ActiveSheet.PivotTables("Location3Table").PivotFields("Rating")
        .Orientation = xlColumnField
        .Position = 1
    End With
On Error Resume Next
    ActiveSheet.PivotTables("Location3Table").PivotSelect "Focus", xlDataAndLabel, _
        True
'    On Error Resume Next
    ActiveSheet.PivotTables("Location3Table").PivotFields("Rating").PivotItems("Focus" _
        ).Position = 1
'    On Error Resume Next
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
'    On Error Resume Next
    ActiveSheet.PivotTables("Location3Table").PivotSelect "High", xlDataAndLabel, True
'    On Error Resume Next
    With Selection.Font
        .Color = -16727809
        .TintAndShade = 0
    End With
    Range("A5").Select
    ActiveSheet.PivotTables("Location3Table").PivotFields("Host Type").AutoSort _
        xlDescending, "Count of Rating"
    ActiveSheet.PivotTables("Location3Table").PivotFields("Host Type").ShowDetail = _
        False
    ActiveSheet.PivotTables("Location3Table").ShowTableStyleRowStripes = True
'
' Move Location3's worksheets to new Workbook
'
    Sheets(Array("Location3_Pivot", "Location3")).Select
    Sheets("Location3").Activate
    Sheets(Array("Location3_Pivot", "Location3")).Move
    Sheets("Location3").Activate
    Range("A2").Select
    Sheets("Location3_Pivot").Activate
    Range("A5").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\e305779\Downloads\EVAN Scans Working Folder\Hosts with Vulnerabilities Location3.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
'
' Copy Location4's Data to Location4 worksheet
'
    Sheets("Master_Data").Select
    Range("A2").Select
    ActiveSheet.Range("A:Y").AutoFilter Field:=4, Criteria1:= _
        "Location4"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Location4").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2").Select
    ActiveWindow.FreezePanes = True
'
' Create Location4's Pivot Table
'
    Sheets("Location4").Select
    ActiveWorkbook.Names.Add Name:="Location4Data", RefersToR1C1:= _
        "=OFFSET(Location4!R1C1,0,0,COUNTA(Location4!C),COUNTA(Location4!R))"
    ActiveWorkbook.Names("Location4Data").Comment = ""
    Sheets.Add
    Sheets("Sheet10").Select
    Sheets("Sheet10").Name = "Location4_Pivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Location4Data", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Location4_Pivot!R3C1", TableName:="Location4Table", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Location4_Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Location4Table").PivotFields("Host Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Location4Table").PivotFields("NBName")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Location4Table").PivotFields("Vulnerability Name")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("Location4Table").PivotFields("IP Address")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("Location4Table").AddDataField ActiveSheet.PivotTables( _
        "Location4Table").PivotFields("Rating"), "Count of Rating", xlCount
    With ActiveSheet.PivotTables("Location4Table").PivotFields("Rating")
        .Orientation = xlColumnField
        .Position = 1
    End With
On Error Resume Next
    ActiveSheet.PivotTables("Location4Table").PivotSelect "Focus", xlDataAndLabel, _
        True
'    On Error Resume Next
    ActiveSheet.PivotTables("Location4Table").PivotFields("Rating").PivotItems("Focus" _
        ).Position = 1
'    On Error Resume Next
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
'    On Error Resume Next
    ActiveSheet.PivotTables("Location4Table").PivotSelect "High", xlDataAndLabel, True
'    On Error Resume Next
    With Selection.Font
        .Color = -16727809
        .TintAndShade = 0
    End With
    Range("A5").Select
    ActiveSheet.PivotTables("Location4Table").PivotFields("Host Type").AutoSort _
        xlDescending, "Count of Rating"
ActiveSheet.PivotTables("Location4Table").PivotFields("Host Type").ShowDetail = _
        False
    ActiveSheet.PivotTables("Location4Table").ShowTableStyleRowStripes = True
'
' Move Location4's worksheets to new Workbook
'
    Sheets(Array("Location4_Pivot", "Location4")).Select
    Sheets("Location4").Activate
    Sheets(Array("Location4_Pivot", "Location4")).Move
    Sheets("Location4").Activate
    Range("A2").Select
    Sheets("Location4_Pivot").Activate
    Range("A5").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\e305779\Downloads\EVAN Scans Working Folder\Hosts with Vulnerabilities Location4.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
'
' Copy Location5's Data to Location5 worksheet
'
    Sheets("Master_Data").Select
    Range("A2").Select
    ActiveSheet.Range("A:Y").AutoFilter Field:=4, Criteria1:= _
        "Location5"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Location5").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2").Select
    ActiveWindow.FreezePanes = True
'
' Create Location5's Pivot Table
'
    Sheets("Location5").Select
    ActiveWorkbook.Names.Add Name:="Location5Data", RefersToR1C1:= _
        "=OFFSET(Location5!R1C1,0,0,COUNTA(Location5!C),COUNTA(Location5!R))"
    ActiveWorkbook.Names("Location5Data").Comment = ""
    Sheets.Add
    Sheets("Sheet11").Select
    Sheets("Sheet11").Name = "Location5_Pivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Location5Data", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Location5_Pivot!R3C1", TableName:="Location5Table", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Location5_Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Location5Table").PivotFields("Host Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Location5Table").PivotFields("NBName")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Location5Table").PivotFields("Vulnerability Name")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("Location5Table").PivotFields("IP Address")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("Location5Table").AddDataField ActiveSheet.PivotTables( _
        "Location5Table").PivotFields("Rating"), "Count of Rating", xlCount
    With ActiveSheet.PivotTables("Location5Table").PivotFields("Rating")
        .Orientation = xlColumnField
        .Position = 1
    End With
On Error Resume Next
    ActiveSheet.PivotTables("Location5Table").PivotSelect "Focus", xlDataAndLabel, _
        True
'    On Error Resume Next
    ActiveSheet.PivotTables("Location5Table").PivotFields("Rating").PivotItems("Focus" _
        ).Position = 1
'    On Error Resume Next
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
'    On Error Resume Next
    ActiveSheet.PivotTables("Location5Table").PivotSelect "High", xlDataAndLabel, True
'    On Error Resume Next
    With Selection.Font
        .Color = -16727809
        .TintAndShade = 0
    End With
    Range("A5").Select
    ActiveSheet.PivotTables("Location5Table").PivotFields("Host Type").AutoSort _
        xlDescending, "Count of Rating"
ActiveSheet.PivotTables("Location5Table").PivotFields("Host Type").ShowDetail = _
        False
    ActiveSheet.PivotTables("Location5Table").ShowTableStyleRowStripes = True
'
' Move Location5's worksheets to new Workbook
'
    Sheets(Array("Location5_Pivot", "Location5")).Select
    Sheets("Location5").Activate
    Sheets(Array("Location5_Pivot", "Location5")).Move
    Sheets("Location5").Activate
    Range("A2").Select
    Sheets("Location5_Pivot").Activate
    Range("A5").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\e305779\Downloads\EVAN Scans Working Folder\Hosts with Vulnerabilities Location5.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
'
' Un-Filter Master Data worksheet
'
    Sheets("Master_Data").Select
    ActiveSheet.Range("A:Y").AutoFilter Field:=4
    Range("A2").Select
'
    Sheets("MasterPivot").Select
    ActiveSheet.PivotTables("MasterPivot").ShowTableStyleRowStripes = True
'
   ActiveWorkbook.Save
   ActiveWorkbook.Close
    End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
"Wow"

There is a lot of code that could be shortened like all the "Select" stuff

Like this:

Columns("T:T").Select
Selection.ColumnWidth = 38

Needs to be changed to this:

Columns("T:T").ColumnWidth = 38


There is no need to do all the selecting

And there are a thousand like this.

Someone could spend a whole day fixing all this
You should walk through all this code and clean it up.
For someone who says "I am by no means a <acronym title="visual basic for applications">VBA</acronym> coder"

This is a really big task your attempting.
I always start off small and move along to larger scripts as I learn more
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
VBA Dynamic PivotTable Name
Replies
4
Views
480
th259
T
C
  • Solved
VBA - Need help optimize code that's updating pivot tables.
Replies
1
Views
77
Cubist
C
B
  • Solved
Recording macro pivot table error
Replies
2
Views
977
biglb79
B
U
  • Solved
Re-organizing pivot table fields in vba
Replies
2
Views
579
ummjay
U
U
  • Solved
VBA question involving 2 different workbooks
Replies
2
Views
403
ummjay
U

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top