How to save excel sheet as new name when a cell is changed?
Hello! I'm trying to create a macro that saves the current worksheet as the current date and time when I paste something on the sheet. I get an error on the 'If intersect..' line, saying
Runtime error 1004
Method 'Range' of object '_Global' failed
Sub savedate()If Intersect(Target, Range(“A1”)) Is Nothing ThenExit SubElse ActiveSheet.SaveAs ("Libraries\Documents\Tempcode" & format(Now(), "DD-MMM-YYYY hh mm AMPM") & ".xlsx")End IfEnd Sub
The purpose of this is because i'm copying and pasting data into this workbook, and will want to track the date/time that this data was extracted. This data is actually the script of a webpage but I'm unable to incorporate the date when I'm copying it, so I am trying to save the date when I paste it in my workbook. I will use the saved sheet for subsequent steps.
I'm not sure what the error means, and even so, I don't think this will save the worksheet upon pasting. I think I will have to activate the macro once I paste. I'm open to any other methods to save the date/time upon pasting the data. Hope you can help me with the code, thank you!
This is the current full code but I'm having another issue: once the macro runs, the new workbook (with the date and time as file name) will be active and the original workbook will be closed. I'm wondering if I can keep the original workbook (with macros) to be active and have the new workbook to close. If possible, I'd also like to just save the current worksheet instead of the entire workbook.
Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, Range("A1")) Is Nothing Then Exit SubElseDim filename As Stringfilename = format(Now(), "DD-MMM-YYYY hh mm AMPM") & ".xlsx" ActiveSheet.SaveAs filename:=filename, FileFormat:= _xlOpenXMLWorkbook, CreateBackup:=FalseEnd IfEnd Sub
Open in new window
for it to work.This is the current full code but I'm having another issue: once the macro runs, the new workbook (with the date and time as file name) will be active and the original workbook will be closed. I'm wondering if I can keep the original workbook (with macros) to be active and have the new workbook to close. If possible, I'd also like to just save the current worksheet instead of the entire workbook.
Open in new window