SOLVED
Home

Button to jump and autofill?

%3CLINGO-SUB%20id%3D%22lingo-sub-658064%22%20slang%3D%22en-US%22%3EButton%20to%20jump%20and%20autofill%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658064%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20create%20a%20button%20at%20A1%20that%20will%20(1)%20jump%20to%20the%20first%20blank%20cell%20in%20that%20column%26nbsp%3Band%20(2)%20fill%20that%20cell%20with%20today's%20date%26nbsp%3B(as%20a%20value%2C%20not%20a%20function)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20my%20first%20attempt%2C%20I%20created%20a%20hyperlink%20that%20will%20go%20to%20the%20first%20blank%20cell%20in%20that%20column.%26nbsp%3B%20The%20trouble%20is%20that%20this%20workbook%20has%2035%20sheets%20(!)%20and%20I'd%20need%20such%20a%20hyperlink%20on%20each%20sheet.%26nbsp%3B%20Having%20that%20many%20hyperlinks%20REALLY%20consumes%20memory%20and%20slows%20everything%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20is%20it%20possible%20to%20do%20this%20by%20putting%20a%20macro%20on%20a%20button%2C%20by%20VBA%20code%2C%20or%20by%20some%20other%20method%26nbsp%3Bthat%20doesn't%20slow%20everything%20down%20to%20a%20crawl%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-658064%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658302%22%20slang%3D%22en-US%22%3ERe%3A%20Button%20to%20jump%20and%20autofill%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658302%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20do%20that%20using%20this%20code%3A%3C%2FP%3E%3CPRE%3ESub%20JumpToTheFirstBlankCell()%3CBR%20%2F%3E%20%20Range(%22A1%22).End(xlDown).Activate%3CBR%20%2F%3E%20%20ActiveCell.End(xlDown).Offset(1%2C%200).Select%3CBR%20%2F%3E%20%20Selection.Value%20%3D%20Date%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20out%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662595%22%20slang%3D%22en-US%22%3ERe%3A%20Button%20to%20jump%20and%20autofill%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662595%22%20slang%3D%22en-US%22%3E%3CP%3E(Sorry%20for%20the%20delay%20getting%20back%20to%20you%20but%20I've%20been%20travelling...)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%2C%20This%20is%20GREAT%20and%20I%20*really*%20appreciate%20your%20help!%26nbsp%3B%20It%20is%20very%20nice%20of%20you%20to%20go%20to%20so%20much%20trouble%20to%20even%20create%2035%20tabs%20for%20testing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20while%20your%20sample%20workbook%20works%20great%2C%20your%20button%20doesn't%20work%20on%20mine%20(small%20sample%20attached).%26nbsp%3B%20A%20button%20click%20always%20goes%20to%20A6.%26nbsp%3B%20I%20want%20it%20to%20go%20to%20A45%20in%20this%20case%2C%20of%20course.%26nbsp%3B%20(I%20would%20understand%20if%20it%20went%20to%20A2%20or%20A4%20and%20I'd%20find%20a%20work-around%20for%20that%20-%20like%20putting%20a%20dummy%20value%20in%20those%20cells).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20must%20be%20something%20about%20the%20values%2Fformat%20of%20my%20cells%20which%20is%20breaking%20your%20code%20but%20I%20can't%20figure%20what%20that%20might%20be.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20for%20your%20expert%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662613%22%20slang%3D%22en-US%22%3ERe%3A%20Button%20to%20jump%20and%20autofill%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20solution%20is%20to%20make%20the%20starting%20cell%20in%20the%20macro%20always%20the%20first%20left-most%20cell%20in%20the%20table%20which%20is%20A5.%3C%2FP%3E%3CP%3EWhich%20require%20changing%20the%20code%20a%20little%20bit%20as%20follows%3A%3C%2FP%3E%3CPRE%3ESub%20JumpToTheFirstBlankCell()%3CBR%20%2F%3E%20%20Range(%22A5%22).End(xlDown).Offset(1%2C%200).Select%3CBR%20%2F%3E%20%20Selection.Value%20%3D%20Date%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116850i707213A855C42D4F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot_1.png%22%20title%3D%22Screenshot_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662696%22%20slang%3D%22en-US%22%3ERe%3A%20Button%20to%20jump%20and%20autofill%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662696%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20works%20great%2C%20thanks%20again!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20notice%20that%20you%20combined%20your%20first%20two%20lines%20of%20code.%26nbsp%3B%20I'm%20still%20not%20sure%20how%20they%20work%20but%20you've%20inspired%20me%20to%20buy%20a%20book%20on%20VBA%20because%20it%20seems%20that%20it%20will%20open%20up%20a%20whole%20new%20world%20of%20possibilities%20for%20my%20workbooks.%26nbsp%3B%20I%20might%20even%20figure%20out%20how%20to%20add%20a%20Tab%20(to%20move%20to%20the%20next%20column)%20at%20the%20end%20of%20your%20macro.%20%3A-)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20I%20very%20much%20appreciate%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662809%22%20slang%3D%22en-US%22%3ERe%3A%20Button%20to%20jump%20and%20autofill%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662809%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20is%20very%20simple!%3C%2FP%3E%3CPRE%3ERange(%22A5%22).End(xlDown).Offset(1%2C%200).Select%3C%2FPRE%3E%3CP%3EI%20used%3CSTRONG%3E%20Range(%22A5%22)%3C%2FSTRONG%3E%20to%20start%20with%20range%20A5%2C%20then%20used%20%3CSTRONG%3EEnd(xlDown)%20%3C%2FSTRONG%3Eto%20move%20down%20to%20the%20last%20filled%20cell%20in%20the%20range%20as%20if%20you%20were%20pressing%20the%20keyboard%20shortcut%20%3CSTRONG%3E(Ctrl%2BDown%20Arrow).%3C%2FSTRONG%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20last%20portion%20of%20the%20line%20%3CSTRONG%3EOffset(1%2C%200).Select%3C%2FSTRONG%3E%20used%20to%20move%20down%20one%20row%20to%20the%20first%20blank%20cell%20and%20select%20that%20cell.%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20move%20to%20the%20next%20column%20change%20it%20as%20follows%3A%20%3CSTRONG%3EOffset(1%2C%201).Select%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20the%20second%20line%20is%20clear!%3C%2FP%3E%3CPRE%3ESelection.Value%20%3D%20Date%3C%2FPRE%3E%3CP%3EIt%20sets%20the%20value%20of%20the%20selected%20cell%20to%20the%20current%20date%2C%20where%20%3CSTRONG%3EDate%20%3C%2FSTRONG%3Eis%20the%20VBA%20equivalent%20to%20the%20worksheet%20function%20%3CSTRONG%3ETODAY()%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662886%22%20slang%3D%22en-US%22%3ERe%3A%20Button%20to%20jump%20and%20autofill%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662886%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BActually%2C%20I%20wanted%20it%20to%20fill%20in%20the%20date%20using%20your%20code%20and%20then%20move%20one%20column%20to%20the%20right.%26nbsp%3B%20So%20I%20added%20this%20at%20the%20end%20of%20your%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%20ActiveCell.Offset(0%2C%201).Range(%22A1%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20confess%20that%20I%20cheated.%26nbsp%3B%20I%20recorded%20a%20macro%20and%20then%20looked%20at%20the%20code%20that%20it%20generated.%26nbsp%3B%20%3A-)%26nbsp%3B%20But%20now%20I%20have%20exactly%20what%20I%20wanted!%3C%2FP%3E%3CP%3EThanks%20again%20for%20all%20of%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662894%22%20slang%3D%22en-US%22%3ERe%3A%20Button%20to%20jump%20and%20autofill%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20this%20is%20common%20practice%2C%20I%20also%20do%20it%20sometimes.%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3EI%20confess%20that%20I%20cheated.%20I%20recorded%20a%20macro%20and%20then%20looked%20at%20the%20code%20that%20it%20generated.%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E
bvelke
Occasional Contributor

Is it possible to create a button at A1 that will (1) jump to the first blank cell in that column and (2) fill that cell with today's date (as a value, not a function)?

 

As my first attempt, I created a hyperlink that will go to the first blank cell in that column.  The trouble is that this workbook has 35 sheets (!) and I'd need such a hyperlink on each sheet.  Having that many hyperlinks REALLY consumes memory and slows everything down.

 

So is it possible to do this by putting a macro on a button, by VBA code, or by some other method that doesn't slow everything down to a crawl?

 

Thanks for any help.

7 Replies

Hi @bvelke,

 

You can do that using this code:

Sub JumpToTheFirstBlankCell()
Range("A1").End(xlDown).Activate
ActiveCell.End(xlDown).Offset(1, 0).Select
Selection.Value = Date
End Sub

 

Please check out the attached file.

 

Hope that helps

Highlighted

(Sorry for the delay getting back to you but I've been travelling...)

 

@Haytham Amairah , This is GREAT and I *really* appreciate your help!  It is very nice of you to go to so much trouble to even create 35 tabs for testing.

 

Unfortunately, while your sample workbook works great, your button doesn't work on mine (small sample attached).  A button click always goes to A6.  I want it to go to A45 in this case, of course.  (I would understand if it went to A2 or A4 and I'd find a work-around for that - like putting a dummy value in those cells).

 

There must be something about the values/format of my cells which is breaking your code but I can't figure what that might be.

 

Thanks again for your expert help!

Solution

@bvelke

 

The solution is to make the starting cell in the macro always the first left-most cell in the table which is A5.

Which require changing the code a little bit as follows:

Sub JumpToTheFirstBlankCell()
Range("A5").End(xlDown).Offset(1, 0).Select
Selection.Value = Date
End Sub

Screenshot_1.png

 

Hope that helps

That works great, thanks again!

 

I notice that you combined your first two lines of code.  I'm still not sure how they work but you've inspired me to buy a book on VBA because it seems that it will open up a whole new world of possibilities for my workbooks.  I might even figure out how to add a Tab (to move to the next column) at the end of your macro. :-)

 

Again, I very much appreciate your help.

@bvelke

 

The code is very simple!

Range("A5").End(xlDown).Offset(1, 0).Select

I used Range("A5") to start with range A5, then used End(xlDown) to move down to the last filled cell in the range as if you were pressing the keyboard shortcut (Ctrl+Down Arrow).

 

The last portion of the line Offset(1, 0).Select used to move down one row to the first blank cell and select that cell.

If you want to move to the next column change it as follows: Offset(1, 1).Select.

 

I think the second line is clear!

Selection.Value = Date

It sets the value of the selected cell to the current date, where Date is the VBA equivalent to the worksheet function TODAY().

 

Hope that helps

@Haytham Amairah Actually, I wanted it to fill in the date using your code and then move one column to the right.  So I added this at the end of your code:

     ActiveCell.Offset(0, 1).Range("A1").Select

 

I confess that I cheated.  I recorded a macro and then looked at the code that it generated.  :-)  But now I have exactly what I wanted!

Thanks again for all of your help.

@bvelke

 

You're welcome.

 

By the way, this is common practice, I also do it sometimes.

I confess that I cheated. I recorded a macro and then looked at the code that it generated.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies