Autofill Formula to last Row in a Column

[SAMCRO2014]

I have been trying to get my macro to auto fill a vlookup formula from cells E2 to the last row based on how many rows data is the spreadsheet "Data". This number will constantly change.

Here is my coding:

Dim Data As Worksheet
Set Data = ThisWorkbook.Sheets("Data")

Dim LastRowData As Long
LastRowData = Data.Cells(Rows.Count, 1).End(xlUp).Row

' Open Salary GL Workbook to see if the GL numbers are categorized as regular, overtime, premiums, other.

  
    Range("E2").Select
    Workbooks.Open Filename:= _
        "J:\FinanceAdmin\Finance\Resource Management\Regional\2018-19\Salary Reconciliation\Planned vs Actuals\Look up Tables\Salary GL.xlsx"
    Windows("2019.P#.Planned vs Actuals - 1237.v1.RXM.xlsm").Activate
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[Salary GL.xlsx]Page1_1'!R2C1:R49C3,3,0)"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E" & LastRowData)

This is the result I get.

-- corrupted image removed --

What I am doing wrong?

 

[SAMCRO2014]

I have to post the entire macro. The changes are in BOLD

Planned_VS_Actuals_1237 Macro

' Set variables
Dim PPDR As Worksheet
Set PPDR = ThisWorkbook.Sheets("PPDR")

Dim SPS As Worksheet
Set SPS = ThisWorkbook.Sheets("SPS+")

Dim Data As Worksheet
Set Data = ThisWorkbook.Sheets("Data")

Dim LastRowText As Long
LastRowText = PPDR.Cells(Rows.Count, "P").End(xlUp).Row

Dim LastRowPPDR As Long
LastRowPPDR = PPDR.Cells(Rows.Count, 1).End(xlUp).Row

Dim LastRowSPS As Long
LastRowSPS = SPS.Cells(Rows.Count, 1).End(xlUp).Row

I had this here but moved it:
Dim LastRowData As Long
LastRowData = Data.Cells(Rows.Count, 1).End(xlUp).Row

Dim p As Long
Dim c As Long


' Turn off printing and screen updating to speed up macro
Application.ScreenUpdating = False

' Select PPDR worksheet and remove background colour and covert green triangles to numbers
PPDR.Select
Cells.Select
With Selection.Interior
.Pattern = x1None
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With


' Resize all columns, sort the data by "Text" column P from Z to A
'
Cells.Select
Cells.EntireColumn.AutoFit
PPDR.Sort.SortFields.Clear
PPDR.Sort.SortFields.Add Key:=Range("P:P") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With PPDR.Sort
.SetRange Range("A:S")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Copy and paste text data from column P and paste in "Pay Period For" column M using a loop

For t = 2 To LastRowText
If Cells(t, 16).Value <> 1 Then
Cells(t, 16).Copy Destination:=PPDR.Cells(t, 13)
End If

Next t

'Delete Column P
Range("P:P").Delete Shift:=xlToLeft

' Copy the PPDR data onto the data tab and make all text general.
Cells.Select
Selection.Copy
Sheets("Data").Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "General"
'
'Delete the following columns:
'CC Description
'Wage Type
'Wage Type Text
'Pay Scale Group
'Pay Scale Level
'Entitlement Start
'Entitlement End

Cells.Select
Selection.Copy
Sheets("Data").Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "General"
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("F:G").Select
Selection.Delete Shift:=xlToLeft
Columns("N:O").Select
Selection.Delete Shift:=xlToLeft
Columns("K:L").Select
Selection.Delete Shift:=xlToLeft

'Insert columns and use text to columns to remove "A" from Functional Area to leave the SSA number
Columns("E:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)), TrailingMinusNumbers:=True
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Range("E1").Select
ActiveCell.FormulaR1C1 = "SSA"

' Insert new column and name it Category
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.NumberFormat = "General"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Category"

'Insert new column and call it Manager
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.NumberFormat = "General"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Manager"


Inserted the declaration here:
Dim LastRowData As Long
LastRowData = Data.Cells(Rows.Count, 1).End(xlUp).Row

' Open Salary GL Workbook to see if the GL numbers are categorized as regular, overtime, premiums, other.


Workbooks.Open Filename:= _
"J:\FinanceAdmin\Finance\Resource Management\Regional\2018-19\Salary Reconciliation\Planned vs Actuals\Look up Tables\Salary GL.xlsx"
Windows("2019.P#.Planned vs Actuals - 1237.v1.RXM.xlsm").Activate
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'[Salary GL.xlsx]Page1_1'!R2C1:R49C3,3,0)"
Selection.AutoFill Destination:=Range("E2:E" & LastRowData)

'Close Salary GL Workbook

Windows("Salary GL.xlsx").Activate
ActiveWorkbook.Close

'Open CC by SSA by Manager workbook to see which manager is responsible for the CC

Workbooks.Open Filename:= _
"J:\FinanceAdmin\Finance\Resource Management\Regional\2018-19\Salary Reconciliation\Planned vs Actuals\Look up Tables\2019.CC by SSA by Manager.v1.RXM.xlsx"
Windows("2019.P#.Planned vs Actuals - 1237.v1.RXM.xlsm").Activate
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'[2019.CC by SSA by Manager.v1.RXM.xlsx]Sheet1'!R2C2:R558C7,6,0)"
Selection.AutoFill Destination:=Range("F2:F" & LastRowData)


'Close CC by SSA by Manager workbook
Windows("2019.CC by SSA by Manager.v1.RXM.xlsx").Activate
ActiveWorkbook.Close

' Correct column width on column E,F,G,L
Columns("E:E").AutoFit
Columns("F:F").AutoFit
Columns("G:G").AutoFit
Columns("L:L").AutoFit

Stop

 

[Joe4]

In between where you originally had those lines and where you currently have those lines, you are copying data to the "Data" sheet.
Remember, that the LastRowData calculation is NOT a fluid value. It calculates exactly what it is at that point in the code, and does not change, even if you change that data on the sheet afterwards.
So, it looks like you were initially calculating it before you had any data on that sheet, instead of waiting until after you put data on it.

 

[SAMCRO2014]

OK. I thought I had to declare all my variables at the beginning. Make sense now!

 

[Joe4]

You typically declare them at the beginning of the code, but you don't set the values there. You set there values wherever it is appropriate to.
The value are "set in stone" at the point in which they are run and do not change as your data change, unless you set them again later in your code.

So, typically this line would be at the top of your code (along with all your other Dim statements):

[B]Dim LastRowData As Long[/B]

But this line should be placed where it makes sense to do the calculation (after all your data is in there):

[B]
LastRowData = Data.Cells(Rows.Count, 1).End(xlUp).Row[/B]