Macro works when ran on sheet1 but not when ran from sheet2

[Welearnaswego]

Hello.
Fairly new to excel vba, learning on the go as the name suggests. This forum is a godsend by the way. The sheet I'm working on is Excel 97-2003.

I created a macro that filters raw data from an exported company report. The macro works flawlessly when ran from the sheet where the data is located, but not if ran from a sheet that the data is not in.
Realistically I could just use the macro where it works but I would really like to learn from this. Ideally I want to be able to paste the raw data into the 1st sheet and assign the macro to a button on a 2nd sheet, but that can be done later. It might be worth mentioning, I originally recorded the macro as a template, I later went into the code to clean up all the values. Again, I'm new so I'm open to any helpful suggestions.

Sub Organize()
    
    Dim ws As Worksheet
    Set ws = Sheets("Data")
    Dim lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
    
    Application.ScreenUpdating = False
    
    ws.Activate
    ws.Rows("1:1").Insert
    
    
    'Formulas
    ws.Range("L2").FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""P"",RC[-11])),RC[-11],"""")"
    ws.Range("L2").Copy
    ws.Range("L2:L" & lr).PasteSpecial xlPasteAll
    ws.Range("L2:L" & lr).Copy
    ws.Range("L2:L" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("M2").FormulaR1C1 = "=IF(LEN(RC[-11])>3,RC[-11],RC[-1])"
    ws.Range("M2").Copy
    ws.Range("M2:M" & lr).PasteSpecial xlPasteAll
    ws.Range("M2:M" & lr).Copy
    ws.Range("M2:M" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("N2").FormulaR1C1 = "=IF(LEN(RC[-12])>3,RC[-11],RC[-1])"
    ws.Range("N2").Copy
    ws.Range("N2:N" & lr).PasteSpecial xlPasteAll
    ws.Range("N2:N" & lr).Copy
    ws.Range("N2:N" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("O2").FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""P"",RC[-14])),RC[-12],IF(ISNUMBER(SEARCH(""Item"",RC[-14])),"""",RC[-1]))"
    ws.Range("O2").Copy
    ws.Range("O2:O" & lr).PasteSpecial xlPasteAll
    ws.Range("O2:O" & lr).Copy
    ws.Range("O2:O" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("P2").FormulaR1C1 = "=IF(MID(RC[-12],3,1)=""-"",RC[-12],"""")"
    ws.Range("P2").Copy
    ws.Range("P2:P" & lr).PasteSpecial xlPasteAll
    ws.Range("P2:P" & lr).Copy
    ws.Range("P2:P" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("Q2").FormulaR1C1 = "=IF(MID(RC[-12],3,1)=""-"",RC[-12],"""")"
    ws.Range("Q2").Copy
    ws.Range("Q2:Q" & lr).PasteSpecial xlPasteAll
    ws.Range("Q2:Q" & lr).Copy
    ws.Range("Q2:Q" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("R2").FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""Item"",RC[-17])),RC[-13],R[-1]C)"
    ws.Range("R2").Copy
    ws.Range("R2:R" & lr).PasteSpecial xlPasteAll
    ws.Range("R2:R" & lr).Copy
    ws.Range("R2:R" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("S2").FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""P"",RC[-18])),RC[-13],IF(ISNUMBER(SEARCH(""Item"",RC[-18])),"""",RC[-1]))"
    ws.Range("S2").Copy
    ws.Range("S2:S" & lr).PasteSpecial xlPasteAll
    ws.Range("S2:S" & lr).Copy
    ws.Range("S2:S" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("T2").FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""P"",RC[-19])),RC[-13],IF(ISNUMBER(SEARCH(""Item"",RC[-19])),"""",RC[-1]))"
    ws.Range("T2").Copy
    ws.Range("T2:T" & lr).PasteSpecial xlPasteAll
    ws.Range("T2:T" & lr).Copy
    ws.Range("T2:T" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("U2").FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""P"",RC[-20])),RC[-13],IF(ISNUMBER(SEARCH(""Item"",RC[-20])),"""",RC[-1]))"
    ws.Range("U2").Copy
    ws.Range("U2:U" & lr).PasteSpecial xlPasteAll
    ws.Range("U2:U" & lr).Copy
    ws.Range("U2:U" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    ws.Range("V2").FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""P"",RC[-21])),RC[-13],IF(ISNUMBER(SEARCH(""Item"",RC[-21])),"""",RC[-1]))"
    ws.Range("V2").Copy
    ws.Range("V2:V" & lr).PasteSpecial xlPasteAll
    ws.Range("V2:V" & lr).Copy
    ws.Range("V2:V" & lr).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    'Delete Item Key Rows
    ws.Range("$A:$Y").AutoFilter Field:=1, Criteria1:= _
        "Itemkey:"
    ws.Range("A2:A" & lr).Delete Shift:=xlUp
    
    If Sheets("Data").AutoFilterMode Then
        Sheets("Data").ShowAllData
    End If
    
    'Delete Dummy Columns
    ws.Columns("A:K").Delete
    
    
    'Formatting Header Row
    ws.Range("A1").FormulaR1C1 = "PO #"
    ws.Range("B1").FormulaR1C1 = "Item Key"
    ws.Range("C1").FormulaR1C1 = "Description"
    ws.Range("D1").FormulaR1C1 = "Vendor"
    ws.Range("E1").FormulaR1C1 = "Order Date"
    ws.Range("F1").FormulaR1C1 = "Req Date"
    ws.Range("G1").FormulaR1C1 = "Loc"
    ws.Range("H1").FormulaR1C1 = "Qty Rcv"
    ws.Range("I1").FormulaR1C1 = "Qty Rem"
    ws.Range("J1").FormulaR1C1 = "Unit"
    ws.Range("K1").FormulaR1C1 = "Price"
    ws.Cells.EntireColumn.AutoFit
    ws.Columns("A:A").ColumnWidth = 11
    ws.Columns("E:E").ColumnWidth = 10
    ws.Columns("F:F").ColumnWidth = 10
    ws.Columns("G:G").ColumnWidth = 7.5
    ws.Columns("H:H").ColumnWidth = 9
    ws.Columns("I:I").ColumnWidth = 9
    ws.Columns("J:J").ColumnWidth = 9
    ws.Rows("1:1").Font.Bold = True
    ws.Rows("1:1").RowHeight = 27
    
    ws.Columns("E:E").NumberFormat = "mm/dd/yy;@"
    ws.Columns("F:F").NumberFormat = "mm/dd/yy;@"
    ws.Range("H:H").NumberFormat = "#,##0"
    ws.Range("I:I").NumberFormat = "#,##0"
    ws.Columns("K:K").NumberFormat = "$#,##0.00"
    
    With ws.Rows("1:1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    ws.Activate
    ws.Range("A1").Select
    
    Application.ScreenUpdating = True

End Sub

 

[Rick Rothstein]

You have this at the beginning of your code...

Dim ws As Worksheet
Set ws = Sheets("Data")

After that, with the exception of the calculation for the lr variable, every range mentioned in your code references the sheet assigned to the ws variable, which is the sheet named "Data". It does not matter what your active sheet is, only lr will reference it, all other calculations reference the sheet in ws.

 

[Welearnaswego]

You have this at the beginning of your code...

Dim ws As Worksheet
Set ws = Sheets("Data")

After that, with the exception of the calculation for the lr variable, every range mentioned in your code references the sheet assigned to the ws variable, which is the sheet named "Data". It does not matter what your active sheet is, only lr will reference it, all other calculations reference the sheet in ws.

It really was that simple.
Rather than lr = Cells(Rows.Count, "A").End(xlUp).Row
it had to be lr = ws.Cells(Rows.Count, "A").End(xlUp).Row

Thank you immensely!