Help adding formula to exsisting macro & makeing pivot table work

[pkaskaggs]

Currently this code is saved in Sheet1 of the workbook:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Option Explicit
Sub ReordData()
Application.ScreenUpdating = False
With ActiveSheet
.AutoFilterMode = False
With .Range("Y1", .Range("Y" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="=*Shipped dt*", Operator:=xlOr, Criteria2:="=*Inv dt*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilter Field:=1, Criteria1:="=*Est Comp dt*", Operator:=xlAnd
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
.Columns("Z").EntireColumn.Delete
.Columns("V:W").EntireColumn.Delete
.Columns("O:T").EntireColumn.Delete
.Columns("J:M").EntireColumn.Delete
.Columns("F").EntireColumn.Delete
.Columns("C:D").EntireColumn.Delete
.Columns("G").EntireColumn.Insert
End With
Dim LR As Long, LR2 As Long<o></o>
LR = Worksheets("Dealer List").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("Sheet1").Range("G2:G" & LR2)
.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE)),"""",VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE))"<o></o>
End With<o></o>

Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True<o></o>
Application.ScreenUpdating = True<o></o>
'Added to add columns and add heading
Range("G1").Select
ActiveCell.FormulaR1C1 = "Dealer State"
With ActiveCell.Characters(Start:=1, Length:=12).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("H1").Select
ActiveCell.FormulaR1C1 = "Dealer Name"
Range("H2").Select
LR = Worksheets("Dealer Names").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("Sheet1").Range("H2:H" & LR2)
.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC6,'Dealer Names'!R1C1:R" & LR & "C2,2,FALSE)),"""",VLOOKUP(RC6,'Dealer Names'!R1C1:R" & LR & "C2,2,FALSE))"
End With

End Sub<o></o>
<o></o>
This is stored in Sheet1 on a blank template with four sheets: Sheet1, Summary, Dealer List, Dealer Name. <o></o>
<o></o>
Sheet1 is where exported report is pasted and macro is run to delete columns and enter two columns for Dealer names and locations. On the Sheet1 each line is a specific item, one column contains dates that items will be ready, and another column that designates the destination type of each item("F" or ""). <o></o>
<o></o>
The Dealer List, and Dealer Name sheets are used for vlookups. <o></o>
<o></o>
The Summary sheet is for the pivot tables, and this is where my problems are coming from. <o></o>
<o></o>
We are trying to make the pivot table show both a one week outlook for the destination of items that are going to be ready, sorted by "F" or "" if no F items are nonF. The other table will be a month by month outlook on only "F" type items. This month by month is not added yet until we solve our problem. <o></o>
<o></o>
The pivot tables work great when created after the macro is run and the data trimmed down. However the cell references are thrown out of whack if we open the template with the pivot tables already created, and run the macro. This is because the macro deletes the columns that the tables reference. Also the range of the pivot table is messed up for the same reason. <o></o>
<o></o>
I need a way to insert the formula used to help create the 7 day outlook on the pivot table into the macro so it executes after the sheet1 is formatted. Also I need to find a way to ensure the pivot table’s reference area doesn’t change after the code is run. The formula we came up with was:<o></o>
=IF(Sheet1!J2!>TODAY(),1,0)+IF(Sheet1!J2!<?XML:NAMESPACE PREFIX = TODAY()+7,1,0)<o /><TODAY()+7,1,0)<o>
<o></o>
I hope this is clear here are screen shots to help understanding. Also any help cleaning up the already written code would also be welcomed. <o></o>
<o></o>
<o>Summary

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 338px"><COL style="WIDTH: 114px"><COL style="WIDTH: 68px"><COL style="WIDTH: 68px"><COL style="WIDTH: 68px"><COL style="WIDTH: 61px"><COL style="WIDTH: 61px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Count of VM_VIN</TD><TD>Column Labels</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Row Labels</TD><TD style="TEXT-ALIGN: right">7/28/2010</TD><TD style="TEXT-ALIGN: right">7/29/2010</TD><TD style="TEXT-ALIGN: right">7/30/2010</TD><TD style="TEXT-ALIGN: right">7/31/2010</TD><TD style="TEXT-ALIGN: right">8/1/2010</TD><TD style="TEXT-ALIGN: right">8/2/2010</TD><TD>Grand Total</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: left">F</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">74</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">14</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: left">DFW AIRPORT,TX/ENTERPRISE RENT A CAR</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: left">DUNCANVILLE,TX/METROPLEX TOYOTA</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: left">GRAPEVINE,TX/TEXAS TOYOTA OF GRAPEVINE</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: left">HOUSTON,TX/NATIONAL RAC - HOBBY AIRPORT</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: left">HOUSTON,TX/OPEN ROAD RENT A CAR</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD style="TEXT-ALIGN: right">19</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: left">HOUSTON,TX/STERLING MCCALL TOYOTA</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: left">HOUSTON,TX/VANGUARD CAR RENTAL</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: left">HUMBLE,TX/OPEN ROAD RENT A CAR</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: left">IRVING,TX/TOYOTA OF IRVING, INC.</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: left">KATY,TX/HAROPEN ROAD RENT A CAR</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: left">SACRAMENTO,CA/HERTZ RENT A CAR</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: left">SAN ANTONIO,TX/BUDGET CAR & TRUCK RENTAL</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: left">SAN ANTONIO,TX/ENTERPRIZE RAC</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: left">SPRING,TX/OPEN ROAD RENT A CAR</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: left">TYLER,TX/CLASSIC TOYOTA,OLDS,MER-B</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">2</TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #000000"></TD><TD style="BACKGROUND-COLOR: #000000; TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: left">Non Fleet</TD><TD style="TEXT-ALIGN: right">158</TD><TD style="TEXT-ALIGN: right">212</TD><TD style="TEXT-ALIGN: right">1621</TD><TD style="TEXT-ALIGN: right">403</TD><TD style="TEXT-ALIGN: right">387</TD><TD style="TEXT-ALIGN: right">345</TD><TD style="TEXT-ALIGN: right">3126</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD></TD><TD style="TEXT-ALIGN: right">157</TD><TD style="TEXT-ALIGN: right">211</TD><TD style="TEXT-ALIGN: right">1617</TD><TD style="TEXT-ALIGN: right">400</TD><TD style="TEXT-ALIGN: right">384</TD><TD style="TEXT-ALIGN: right">343</TD><TD style="TEXT-ALIGN: right">3112</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: left">HOUSTON,TX/G.</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: left">LEWISVILLE,TX/RENE ISIP OF LEWISVILLE</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: left">LITTLE ROCK,AR/LANDERS </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: left">WESTFIELD,TX/ INC.</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: left">Grand Total</TD><TD style="TEXT-ALIGN: right">172</TD><TD style="TEXT-ALIGN: right">229</TD><TD style="TEXT-ALIGN: right">1642</TD><TD style="TEXT-ALIGN: right">415</TD><TD style="TEXT-ALIGN: right">394</TD><TD style="TEXT-ALIGN: right">348</TD><TD style="TEXT-ALIGN: right">3200</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </o>
<o></o>
</TODAY()+7,1,0)<o>

 

[pkaskaggs]

I have been working on it today and trimmed my code to this

Option Explicit
Sub ReordData()
Application.ScreenUpdating = False
With ActiveSheet
.AutoFilterMode = False
With .Range("Y1", .Range("Y" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="=*Shipped dt*", Operator:=xlOr, Criteria2:="=*Inv dt*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilter Field:=1, Criteria1:="=*Est Comp dt*", Operator:=xlAnd
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
.Columns("Z").EntireColumn.Delete
.Columns("V:W").EntireColumn.Delete
.Columns("O:T").EntireColumn.Delete
.Columns("J:M").EntireColumn.Delete
.Columns("F").EntireColumn.Delete
.Columns("C:D").EntireColumn.Delete
.Columns("G").EntireColumn.Insert
End With
Dim LR As Long, LR2 As Long
LR = Worksheets("Dealer List").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("Sheet1").Range("G2:G" & LR2)
.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE)),"""",VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE))"
End With
 
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Application.ScreenUpdating = True
'Added to add columns and add heading
With Range("G1").Select
ActiveCell.FormulaR1C1 = "Dealer State"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Dealer Name"
Range("H2").Select
Range("L1").Select
ActiveCell.FormulaR1C1 = "Date Filter"
End With
With LR = Worksheets("Dealer Names").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
End With
 
With Worksheets("Sheet1").Range("H2:H" & LR2)
.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC6,'Dealer Names'!R1C1:R" & LR & "C2,2,FALSE)),"""",VLOOKUP(RC6,'Dealer Names'!R1C1:R" & LR & "C2,2,FALSE))"
End With
'Autofits columns
If ActiveWorkbook Is Nothing Then Exit Sub
Dim i#
If ActiveWindow.SelectedSheets.Count > 1 Then
For i = 1 To ActiveWindow.SelectedSheets.Count
ActiveWindow.SelectedSheets(i).Cells.EntireColumn.AutoFit
Next
Else
Cells.EntireColumn.AutoFit
End If
 
'Range("L2:L50000").Formula = "=IF(Sheet1!$J2>TODAY(),1,0)+IF(Sheet1!$J2<TODAY()+7,1,0)"< p>  
Dim Date_Range As Range
With Worksheets("Sheet1")
Set Date_Range = .Range("L2:L" & .Range("A65536").End(xlUp).Row)
 
Date_Range.Formula = "=IF(Sheet1!$J2>TODAY(),1,0)+IF(Sheet1!$J2<TODAY()+7,1,0)"< p> End With
 
End Sub

Now all I have to do is find out how to insert the pivot table in a seperate sheet. Not to be picky but the code runs very slow any tips for speeding it up would also be appreciated.

Thanks for any help!!