Excel VBA

[esgca2010]

Rush job and I apologize for asking but desperate time calls for desperate measures.

12 Workbooks each workbook for each month, i.e., Jan, Feb, Mar, etc
Each Workbook has 30, 31, 28, or 29 (depending on leap year) worksheets
Cell Q4 has a date for the day of the month: Example: Jan's workbook has 31 worksheets, starting with 1, 2, 3, through 31 each representing the days.

At the beginning of the year, the user has to change the year in each worksheet for each month. For example in Q4 sheet 1 has Jan 1, 2023, changed to Jan 1, 2024, Sheet 2 has Jan 2, 2023, changed to Jan 2, 2024, etc., and for every year after. A simple change to the year but very tedious when working in a hazardous environment.

Can you help me? Fighting a cold and apologize I hope I made myself clear.

Thank you for this great resource.

Elaine

 

[Micron]

If you know at least a bit of VBA find code that loops over workbooks in a directory folder (hopefully yours are all in one folder) and open the wb's hidden. Inside that loop, loop over all the sheets in the opened wb. Assuming the date value is in the same cell in every sheet, edit the date value using DateAdd function to add 1 year. I don't have any code that does all of this; perhaps someone else does and will post it for you, which would be nice because it's not a small task. Once you have it, you can run it once each year. You might want some logic to deal with the possibility that the code could be run when you don't want it to, e.g. if run Jan. 01 it will change 2023 to 2024. If run again, it becomes 2025 in 2024.

The number of workbooks and sheets you have seems to call for a real database and not Excel.

 

[JEC]

It is pretty simple if you have these files in one folder. I can give you base code which you probably need to adapt.
If I understand correctly; In every workbook on every sheet in cell S3, the year should be changed to 2024?

 

[JEC]

It will ask you to select a folder. So make sure all files are located in the same folder (and not in subfolders), without any other files. Otherwise you need to add some lines of code to skip files.

Sub jec()
 Dim xp, sh
 With Application.FileDialog(4)
   If .Show = 0 Then Exit Sub
   Application.ScreenUpdating = False
   xp = Dir(.SelectedItems(1) & "\*xls*")
   Do While xp <> ""
     With Workbooks.Open(.SelectedItems(1) & "\" & xp)
       For Each sh In .Sheets
         If Left(sh.Range("S3"), 2) = "20" Then sh.Range("S3") = sh.Range("S3") + 1
       Next
       .Close 1
     End With
     xp = Dir
   Loop
 End With
End Sub

 

[esgca2010]

It will ask you to select a folder. So make sure all files are located in the same folder (and not in subfolders), without any other files. Otherwise you need to add some lines of code to skip files.

Sub jec()
 Dim xp, sh
 With Application.FileDialog(4)
   If .Show = 0 Then Exit Sub
   Application.ScreenUpdating = False
   xp = Dir(.SelectedItems(1) & "\*xls*")
   Do While xp <> ""
     With Workbooks.Open(.SelectedItems(1) & "\" & xp)
       For Each sh In .Sheets
         If Left(sh.Range("S3"), 2) = "20" Then sh.Range("S3") = sh.Range("S3") + 1
       Next
       .Close 1
     End With
     xp = Dir
   Loop
 End With
End Sub

Thank you! It works. My apologies for not replying sooner but I was under the weather.

All of you at Mr. Excel are great! I truly appreciate all of you!

Respectfully,

Elaine