SaveAs error with Macro

[miketastic]

Hello! I have a question about this thing I'm working on. I'm trying to SaveAs a single sheet from one workbook to a new workbook as a .cvs file. It will always go to the same place with the same name and overwrite the current file.

So far I have this here, but I'm getting errors where the text is red. The new workbook appears with the correct name, but it doesn't save anywhere.

Help is MUCH appreciated.

Sub CopyToCSV()

Dim MyPath As String
Dim MyFileName As String
MyPath = "P:\PAYOFFS\XREF Report\"
MyFileName = "CSVUPLOAD"
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
Sheets("CSV UPLOAD").Copy
With ActiveWorkbook
   [COLOR=#ff0000] .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False[/COLOR]
    .Close False
End With
End Sub

 

[Joe4]

What is the purpose of this line of code here?

Sheets("CSV UPLOAD").Copy

You typically do not have a COPY line unless it it immediately followed by a PASTE line.

If you are trying to copy and paste this sheet to a new workbook and then save it, you need to create a new workbook first, and actually paste the data to that sheet before trying to save it.

Note, that you could also just save the current workbook as a CSV. You will still have your original Excel file.

 

[JoeMo]

What is the purpose of this line of code here?

Sheets("CSV UPLOAD").Copy

You typically do not have a COPY line unless it it immediately followed by a PASTE line.

If you are trying to copy and paste this sheet to a new workbook and then save it, you need to create a new workbook first, and actually paste the data to that sheet before trying to save it.

Note, that you could also just save the current workbook as a CSV. You will still have your original Excel file.

That line will copy the sheet, and since there is no destination given, the default is to copy it to a new workbook which then becomes the active workbook. So, the code the OP posted looks ok to me assuming the file path is a legitimate path.

 

[Joe4]

That line will copy the sheet, and since there is no destination given, the default is to copy it to a new workbook which then becomes the active workbook. So, the code the OP posted looks ok to me assuming the file path is a legitimate path.

Thanks JoeMo. Never knew it would do that.
I don't think I would do it that way (I like we code well-defined, but that's just me!).

Mike,
It might be helpful to know exactly what the error message says. Could you post that?

 

[miketastic]

It might be helpful to know exactly what the error message says. Could you post that?

The error says:

Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed

...and it highlights the line I put in red on my original post.

 

[JoeMo]

The error says:

Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed

...and it highlights the line I put in red on my original post.

Are you certain the path "P:\PAYOFFS\XREF Report\" is a valid path on your computer? Is there a P drive with a folder named PAYOFFS and a sub-folder named XREF Report?

 

[miketastic]

Oh man... I think you might hate me! The path wasn't valid. The drive is named Payoffs and for some stupid reason I put that as a folder. Thank you all very much for the help. It's working now.

 

[JoeMo]

Oh man... I think you might hate me! The path wasn't valid. The drive is named Payoffs and for some stupid reason I put that as a folder. Thank you all very much for the help. It's working now.

I gave a nod to that possibility in post #3. You are welcome - thanks for the reply.