Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > Forums > Excel
 
Excel Forum


Back to 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
 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

6/30/2022Sales Chart
6/17/2022Fast AutoSum
2/22/2022ISO Date Format
1/27/2022Work Days
1/11/2022Excel Fast Tips
1/7/2022Count Cells by Color
1/7/2022Excel VBA
1/7/2022Excel Count by Color
12/26/2021Conditional Formatting
12/9/2021Excel 2003 Quick Start 1
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn