(Solved) – VBA button – based on a cell value rather than ActiveCell


I am very new to VBA and trying to update the code below to look for a value within a cell rather than ActiveCell. Specifically, I want to find the row below a cell with a value of “B.” (e.g.), copy the 3 rows below, and paste insert those 3 rows directly beneath the copied 3 rows. Effectively, I am trying to get my VBA button to work without asking users to first click into a specific cell. My current code, based on ActiveCell, is working well as long as you are in the correct cell. Any insight would be helpful.

Sub CommandButton2_Click()
Dim NextRow As Long
Dim I As Long

With Range(ActiveCell.Offset(rowOffset:=2), ActiveCell.Offset(rowOffset:=0))
    NextRow = .Row   .Rows.Count
    Rows(NextRow & ":" & NextRow   .Rows.Count * (1) - 1).Insert Shift:=xlDown
    .EntireRow.Copy Rows(NextRow & ":" & NextRow   .Rows.Count * (1) - 1)
    .Resize(.Rows.Count * (1   1)).Sort key1:=.Cells(1, 1)
End With

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *