Hello dear experts!
I have a whole bunch of workbooks with 30 sheets each, that I need to update each month. On top of each sheet, I'm using DATE-function: =DATE(2016;10;SHEETS()+14). That helps users telling which date the sheet is made for. Each month I am changing the month (in this case ;10
to the new month. I want to automate this process and therefore I've created a new workbook where the number for the new month automatically is entered and should be implemented in the DATE-functions on alle workbooks and sheets.
Now I've just made this macro:
When I run it, nothing happens. I can't see what I am doing wrong? Can anybody help me?
Thanks!
I have a whole bunch of workbooks with 30 sheets each, that I need to update each month. On top of each sheet, I'm using DATE-function: =DATE(2016;10;SHEETS()+14). That helps users telling which date the sheet is made for. Each month I am changing the month (in this case ;10
Now I've just made this macro:
Code:
Sub New_date()
Dim Findtext As String
Dim Replacetext As String
With Sheets("14")
.Activate
Findtext = "=DATE(2016;10;SHEETS()+14)"
Replacetext = Workbooks("NEWMONTH.xlsm").Sheets("Sheet1").Range("C7").Value
Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
With Sheets("14")
.Activate
DisplayAlerts = False
Findtext = "=DATO(2016;10;ARK()+14)"
Replacetext = Workbooks("NEWMONTH.xlsm").Sheets("Ark1").Range("C7").Value
Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End Sub
When I run it, nothing happens. I can't see what I am doing wrong? Can anybody help me?
Thanks!