troubleshooting Question

How to save excel sheet as new name when a cell is changed?

Avatar of Pearlyn Tan
Pearlyn Tan asked on
VBAMicrosoft ExcelMicrosoft Office
5 Comments2 Solutions140 ViewsLast Modified:
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 Then
Exit Sub
Else
 ActiveSheet.SaveAs ("Libraries\Documents\Tempcode" & format(Now(), "DD-MMM-YYYY hh mm AMPM") & ".xlsx")
End If
End 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!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros