Macro not running on all/correct sheet

[Seraph84]

Hi

I have a workbook with two worksheets. The first worksheet, "Source", basically just has some editing done to it to end up with two columns, and the second worksheet, "Rates", does a vlookup from the first sheet.

The problem I'm experiencing is that the code only works as its supposed to when "Source" is the active sheet. If I run with the "Rates" sheet as active, no editing is done to "Source" sheet. What do I need to do so that the user is able run the macro with the "Rates" sheet open, instead of having to launch it from the "Source" sheet? I plan on adding a button the to the ribbon.

On a side note, when I only have the "Source" code in the book, the macro to edit runs in under a second. Once I add in the "Rates" code, it takes noticeably longer. Is it my code perhaps?

Here's the code:

Sub Lookup()

Dim lrow As Long
Dim SheetName As String

Application.ScreenUpdating = 0

With Sheets("Source")

lrow = Cells(Rows.Count, 1).End(xlUp).Row
lrow2 = Cells(Rows.Count, 4).End(xlUp).Row
lrow3 = Cells(Rows.Count, 7).End(xlUp).Row
lrow4 = Cells(Rows.Count, 11).End(xlUp).Row
lrow5 = Cells(Rows.Count, 5).End(xlUp).Row
lrow6 = Cells(Rows.Count, 14).End(xlUp).Row
lrow7 = Cells(Rows.Count, 15).End(xlUp).Row
lrow8 = Cells(Rows.Count, 13).End(xlUp).Row

With Rows("1:2")
     
     .Delete Shift:=xlUp

End With

With Columns("E:E")

    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End With

Columns("L:O").Delete Shift:=xlToLeft
Columns("G").Delete Shift:=xlToLeft
Columns("H:I").Delete Shift:=xlToLeft

With Columns("D:D")

    .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromAboveOrLeft
    
End With

With Range("D2", Cells(lrow2, "D"))
     .Value = "=TEXT(C2,""dd mmm yy"")"
     .NumberFormat = "@"
     .Value = .Value

End With
     
     
With Columns("G:G")
 
        .Replace What:="Call", Replacement:="(Call)", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
        .Replace What:="Put", Replacement:="(Put)", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
End With


With Range("J2", Cells(lrow4, 11))
     .Value = "=IF(I2="""",H2,I2)"
     .Value = .Value
     
End With

With Columns("G:G")

    .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromAboveOrLeft
    
End With

With Range("G2", Cells(lrow3, 7))
     .Value = "=IF(F2>0,F2,"""")"
     .Value = .Value
     
End With


With Range("N2", Cells(lrow6, 14))
     .Value = "=D2 & "" "" & A2"
     .Value = .Value
     
End With

With Range("O2", Cells(lrow7, 15))
     .Value = "=G2 & "" "" & H2"
     .Value = .Value
     
End With

With Range("M2", Cells(lrow8, 13))
     .Value = "=N2 & "" "" & O2"
     .Value = .Value
     
End With

With Range("A2", Cells(lrow, 1))
     .Value = "=TRIM(M2)"
     .Value = .Value
     
End With

Columns("L:O").Delete Shift:=xlToLeft
Columns("B:J").Delete Shift:=xlToLeft
Columns("A").AutoFit

End With

With Sheets("Rates").Select

 lrow11 = Cells(Rows.Count, 1).End(xlUp).Row
 lrow12 = Cells(Rows.Count, 2).End(xlUp).Row

 
 With Range("A2", Cells(lrow11, 1))

 
    .Replace What:="0 (", Replacement:=" (", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     ReplaceFormat:=False

        
     .Replace What:=".0 (", Replacement:=" (", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 
 End With

 
 With Range("G2", Cells(lrow12, 7))
 
    .Value = "=vlookup(A2,'Source'!A:B,2,0)"
    .Value = .Value

    
 End With
 

 With Columns("A:A")

    
        .Delete Shift:=xlToLeft

        
 End With
 
 
End With


Application.ScreenUpdating = 1


End Sub

Thanks for the help!