Autosave Code Problem

[excellearnerz]

Good day,

I scrounged up a code I dont fully understand, but it seems to work. The goal of the code is that when there are any changes in the cell range I defined, the spreadsheet will autosave/export as a pdf file. However, I want that when another set of changes are submitted to the spreadsheet, it will save again with a sequential file name, and not overwrite the old save.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Step 1: Does the changed range intersect?
If Intersect(Target, Range("A1:AT55")) Is Nothing Then
'Step 2: If there is no intersection, exit procedure
Exit Sub
'Step 3: If there is an intersection, save the workbook
Else
Sheet4.ExportAsFixedFormat xlTypePDF, "C:\Users\Office-PC\Desktop\Destiny\INRToday.pdf"
'Step 4: Close out the If statement
End If
End Sub

Any help will be greatly appreciated.

 

[mole999]

If you make your file name contain date and time 202005051723 it will always be a new name yyyyymmddhhmm

 

[excellearnerz]

Sir,

I have no knowledge on how to begin doing that. If you can provide a code, I will be forever grateful

 

[mole999]

Sir,

I have no knowledge on how to begin doing that. If you can provide a code, I will be forever grateful

Private Sub Worksheet_Change(ByVal Target As Range)
Dim serial As String
'Step 1: Does the changed range intersect?
If Intersect(Target, Range("A1:AT55")) Is Nothing Then
'Step 2: If there is no intersection, exit procedure
Exit Sub
'Step 3: If there is an intersection, save the workbook
Else
serial = Now()
serial = Format(serial, "yyyymmddhhmm")
Sheet4.ExportAsFixedFormat xlTypePDF, "C:\Users\Office-PC\Desktop\Destiny\INRToday" & serial & ".pdf"
'Step 4: Close out the If statement
End If
End Sub

Sub test()
Dim serial As String
serial = Now()
serial = Format(serial, "yyyymmddhhmm")
End Sub

 

[excellearnerz]

Hello Sir,

I tried out your code, now it is not saving at all in the directory. What could have happened here?

The intersected range values "change" due to links from another spreadsheet, this other spreadsheet is where I input the raw data. Does it count as change to the spreadsheet with the macro? I also have an IF function on the spreadsheet, it should count as change right?

 

[Joe4]

mole,

Any reason why you did this over two lines:

serial = Now()
serial = Format(serial, "yyyymmddhhmm")

instead of one, like this?

serial = Format(Now(), "yyyymmddhhmm")

 

[mole999]

mole,

Any reason why you did this over two lines:

serial = Now()
serial = Format(serial, "yyyymmddhhmm")

instead of one, like this?

serial = Format(Now(), "yyyymmddhhmm")

because when someone is learning it can help people to understand the steps involved, it is not my trade, as is evident most of the time

 

[mole999]

Hello Sir,

I tried out your code, now it is not saving at all in the directory. What could have happened here?

The intersected range values "change" due to links from another spreadsheet, this other spreadsheet is where I input the raw data. Does it count as change to the spreadsheet with the macro? I also have an IF function on the spreadsheet, it should count as change right?

if you drop those minor changes does it go back to saving correctly

 

[Joe4]

because when someone is learning it can help people to understand the steps involved, it is not my trade, as is evident most of the time

Gotcha, I often break them up into steps to, so it is easier for them to see what is going on.

 

[excellearnerz]

if you drop those minor changes does it go back to saving correctly

Sorry sir, it seems your code is working perfectly, i tried manually changing cells within the range and I now have multiple files as proof. Now the problem is the linked data, and the IF function within the range is not registering as a change event. This is the final piece for my program to work as I intended, please Sir, help me push through.