Excel Forum

Set Focus with VBA Upload Images Link
Cecil Leforce
11 months ago

I need help with this code to set focus. I would like for it to set focus in column B when the code runs. Thanks

Sub New_Formatted_Row_With_Formula()
Dim rActive As Range

Set rActive = ActiveCell

ActiveSheet.UNPROTECT "9121381"
Application.ScreenUpdating = False

With Cells(Rows.Count, "B").End(xlUp)
    .EntireRow.Copy
    With .Offset(1, 0).EntireRow
        .PasteSpecial xlPasteFormats
        .PasteSpecial xlPasteFormulas
        .PasteSpecial xlPasteValidation
        On Error Resume Next

         .SpecialCells(xlCellTypeConstants).ClearContents
         .SpecialCells(xlCellTypeConstants).ClearNotes
        On Error GoTo 0
    End With
End With

rActive.Select

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    ActiveSheet.Protect "9121381"

End Sub

Scott Axton
11 months ago

Range("B1").Select

Cecil Leforce
11 months ago

I would like for it to set focus in the first cell of the new row that it adds. Also where do i put the new code at in here?
Thanks

Scott Axton
11 months ago

Sorry took me a bit to get back to this.  Try this:

Sub New_Formatted_Row_With_Formula()
  Dim rActive As Range
  Dim LastRowNumber As Long
  Dim NR As Long     'New Row

LastRowNumber = Cells(Rows.Count, 1).End(xlUp).Row
NR = Last_Row_Number + 1

Set rActive = Range("B" & NR)

ActiveSheet.Unprotect "9121381"
Application.ScreenUpdating = False

With Cells(Rows.Count, "B").End(xlUp)
    .EntireRow.Copy
    With .Offset(1, 0).EntireRow
        .PasteSpecial xlPasteFormats
        .PasteSpecial xlPasteFormulas
        .PasteSpecial xlPasteValidation

        On Error Resume Next

         .SpecialCells(xlCellTypeConstants).ClearContents
         .SpecialCells(xlCellTypeConstants).ClearNotes
        On Error GoTo 0
    End With
End With

rActive.Select

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    ActiveSheet.Protect "9121381"

End Sub

Scott Axton
11 months ago

Sorry - just noticed an error.
The line NR = Last_Row_Number + 1
Should be:

NR = LastRowNumber + 1

oops!

Cecil Leforce
11 months ago

Thanks for the help so for but it's still not doing what i would like for it to do. Can i send you the file? If you like you can call if that would help.
Thanks Cecil
423-912-1381

Richard Rost
11 months ago

Hi Cecil. Please make sure you have read the RULES for posting. It's not a problem if you ask someone else to help you, but don't expect them to call you for free support. I don't offer it, and I don't expect my students to either. Answering a few questions here and there in the Forums is one thing. A phone call or sending someone files is another.

Scott Axton
11 months ago

Cecil -
I'm glad to help as I can but right now I'm unable to take on more work. I'm just going into my "busy" season for both of my jobs.
PLUS I've pretty much exhausted my Excel VBA knowledge with what we've done. I'm mostly a db guy.
Feel free to expound on what it's doing (or not doing) and I'll help here if I can.

Alex Hedley
11 months ago

Have you tried the Macro Recorder?
Perform the actions you need and then see what code it produces then amend it.

Ctrl+Shift+Down +1 offset to get the next row

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Excel Forum Comments