Forum Discussion
bvelke
May 30, 2019Brass Contributor
Button to jump and autofill?
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 cre...
- Jun 01, 2019
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 SubHope that helps
Haytham Amairah
May 30, 2019Silver Contributor
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
- bvelkeJun 01, 2019Brass Contributor
(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!
- Haytham AmairahJun 01, 2019Silver Contributor
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 SubHope that helps
- bvelkeJun 01, 2019Brass Contributor
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.