Yes, row 2 is where the headers are and the data follows beginning on row 2. I set row 1003 as a standard boundary because other formulas on other sheets use it. As for the update, I tried your suggestion at first using the following code.</SPAN>
worksheets("Pasted Data").range("A1").currentregion.name = "PT_Data"
thisworkbook.refreshall</SPAN>
This worked originally but I could not get it to work once I added it to my other code. I am pasting the full code below. As you will be a to see I used a series of macros to put it all together. This may be the problem. What I am trying to do is allow cells A1:EI1003 be dynamically updated by the user. Then I want to add cells EJ2:FB1003 with certain formulas. This will all be done in the worksheet labeled, “Pasted Data”. I am using this to update the “Pivot Tables” worksheet. </SPAN>
Sorry for my ignorance, VBA is not my Excel strength. Thanks for all the help!</SPAN>
Sub MacroTest_ALL_17()</SPAN>
'</SPAN>
' MacroTest_ALL_17 Macro</SPAN>
' Adds all 17 additional formulas to the pasted data.</SPAN>
'</SPAN>
' Keyboard Shortcut: Ctrl+Shift+Y</SPAN>
'</SPAN>
Range("EJ2").Select</SPAN>
ActiveCell.FormulaR1C1 = "MONTH#"</SPAN>
Range("EJ3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-62],lookupTables!months,2,FALSE)"</SPAN>
Range("EK2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
Range("EK2").Select</SPAN>
ActiveCell.FormulaR1C1 = "YEAR#"</SPAN>
Range("EK3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VALUE(RC[-64])"</SPAN>
Range("EL2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "FISCAL YEAR"</SPAN>
Range("EL3").Select</SPAN>
ActiveCell.FormulaR1C1 = _</SPAN>
"=IF(fyStart>1,IF(RC[-2]>=fyStart,RC[-65]+1,RC[-65]+0),RC[-65]+0)"</SPAN>
Range("EM2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "CALEN QTR"</SPAN>
Range("EM3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],lookupTables!qtrs,3,FALSE)"</SPAN>
Range("EN2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "FISCAL QTR =VLOOKUP(EJ3,qtrs,4,FALSE)"</SPAN>
Range("EN2").Select</SPAN>
ActiveCell.FormulaR1C1 = "FISCAL QTR"</SPAN>
Range("EN3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],lookupTables!qtrs,4,FALSE)"</SPAN>
Range("EO2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "RES/SUB RES"</SPAN>
Range("EO3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-136]&RC[-135]"</SPAN>
Range("EP2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "BU/BURDEN"</SPAN>
Range("EP3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-139]&RC[-138]"</SPAN>
Range("EQ2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "BU/BC/RES/SUBRES"</SPAN>
Range("EQ3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-140]&RC[-139]&RC[-11]"</SPAN>
Range("ER2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "FTE"</SPAN>
Range("ER3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-64]/mmc"</SPAN>
Range("ES2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Loaded Labor Through COM"</SPAN>
Range("ES3").Select</SPAN>
ActiveCell.FormulaR1C1 = _</SPAN>
"=IF(RC15=R1C149,SUM(RC[-64],RC[-63],RC[-62],RC[-57],RC[-49],RC[-44]),)"</SPAN>
Range("ET2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Loaded ODC Through COM"</SPAN>
Range("ET3").Select</SPAN>
ActiveCell.FormulaR1C1 = _</SPAN>
"=IF(RC15=R1C150,SUM(RC[-60],RC[-58],RC[-50],RC[-46],RC[-45]),)"</SPAN>
Range("EU2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Total COM"</SPAN>
Range("EU3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-51]+RC[-49]+RC[-47]+RC[-46]"</SPAN>
Range("EV2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "TCL + COM"</SPAN>
Range("EV3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-57]+RC[-1]"</SPAN>
Range("EW2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "CLINSLIN"</SPAN>
Range("EW3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=IF(RC[-124]<>R1C153,RC[-124]&RC[-123],0)"</SPAN>
Range("EX2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Option Desc"</SPAN>
Range("EX3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,optDescription,2,FALSE)"</SPAN>
Range("EY2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "WBS Desc"</SPAN>
Range("EY3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC5,wbsDescription,2,FALSE)"</SPAN>
Range("EZ2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "CLIN Desc"</SPAN>
Range("EZ3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC153,clinslindescription,4,FALSE)"</SPAN>
Range("FA2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Site Desc"</SPAN>
Range("FA3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC146,siteDescription,2,FALSE)"</SPAN>
Range("FB2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "RES/SUBRes Desc"</SPAN>
Range("FB3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC145,lgDescription,2,FALSE)"</SPAN>
Range("FB4").Select</SPAN>
Range("ES1").Select</SPAN>
ActiveCell.FormulaR1C1 = "LAB"</SPAN>
Range("ET1").Select</SPAN>
ActiveCell.FormulaR1C1 = "ODC1"</SPAN>
Range("ES2").Select</SPAN>
Range("EJ3:FB3").Select</SPAN>
Selection.AutoFill Destination:=Range("EJ3:FB1003")</SPAN>
Range("EJ3:FB1003").Select</SPAN>
End Sub</SPAN>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)</SPAN>
Worksheets("Pasted Data").Range("A2:FB1003").CurrentRegion.Name = "PT_Data"</SPAN>
ThisWorkbook.RefreshAll</SPAN>
End Sub</SPAN>