Forum Discussion
Solved
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 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.
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
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 SubPlease check out the attached file.
Hope that helps
(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!
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
