Macro needed to update a cell value - complicated by user errors

[lornagreenwood]

I inherited about 1300 workbooks in a single folder that:
I need to update a cell value on 1 worksheet from one variable date to the same date 1 year later.
# of worksheets may vary - there might be 1 worksheet before the targeted tab

I could exist with a macro that merely updates the value and saves the file. I tried to create the macro in the old "record each keystroke" way,
Create new macro, assign keystroke to launch macro
Select cell, F2, backspace, 9 (changing from 2008 to 2009), enter, alt-F, S
and instead I got Visual Basic - and it is:
updating the value to a single specific value,
which is wrong, because the date varies from workbook to workbook
and it won't work in the next workbook. I realize I need to save it in Personal.xls, but there's no point if I can't get it to merely update the value rather than using one specific date.

My DREAM would be for the macro to open a file, when asked to enable macros say yes, if asked to index say no, go to "Contact Info" tab, (perform macro portion above), if asked to index on save say no, close file, get next file in folder until end.

Why oh why don't I still have the option to just record keystrokes to create a macro, & not use Visual Basic? Clearly I'm not worthy of VB!

 

[lenze]

Hi and welcome to the board!!
Can't you use Find And Replace??
FIND: 2008
REPLACE: 2009.
This can also be done with a macro!!

lenze

 

[lornagreenwood]

Thank you! That's a great idea, though it creates a situation with another date on the same sheet I don't want to change (I can work around that.) On "open" the workbook might be on any tab - how do I get it to go to a specific tab/worksheet?

 

[lenze]

In the ThisWorkBookModule

Private Sub Workbook_Open()
Sheet1.Activate
Range("$A$1").Select
End Sub

lenze

 

[lornagreenwood]

Thank you!!!