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 Then
Exit Sub
Else
 ActiveSheet.SaveAs ("Libraries\Documents\Tempcode" & format(Now(), "DD-MMM-YYYY hh mm AMPM") & ".xlsx")
End If
End Sub

Open in new window

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!
Pearlyn TanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zvitamConsultantCommented:
From the error you received it seens that target is not a valid range, Intersect parameters should both be valid ranges.
If target is null then you might get this error.
0
Martin LissOlder than dirtCommented:
Your code should not be in a sub of your making since unless you defined your own 'Target' variable some place, Excel won't recognize it. Instead you should put the code  in the worksheet change event like this.
Private Sub Worksheet_Change(ByVal Target As Range)
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

Open in new window

Secondly, when I copied your code, 'A1' was surrounded by smart quotes and you should change them to regular quotes as in the code I just posted.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pearlyn TanAuthor Commented:
Thanks Martin, that was the main mistake. Relatively new to VBA so this helped a lot. However with your code, I had to add 
FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

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. 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then
    Exit Sub
Else
Dim filename As String
filename = format(Now(), "DD-MMM-YYYY hh mm AMPM") & ".xlsx"
    ActiveSheet.SaveAs filename:=filename, FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
End If
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Please close this question and then ask a new one.
0
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.