VBA Copy Method keeps failing?

Question

I could have sworn that this was working before - but for some reason, this doesn't appear to be working anymore. I'm trying to take the active worksheet (also, this may not be very pretty or clean, but I am still really new to VBA), copy it to a new worksheet, in the new worksheet I want to open the Excel save as dialog, and when the worksheet is saved (in CSV) format, I want the workbook to close (or even if it doesn't close) at least return the user to the original workbook and end the sub

Sub saveExportAs()
    Application.CutCopyMode = False
    Sheets("load").Select
    ActiveWorkbook.Activate
    Sheets("load").Copy
    Dim varResult As Variant
    Dim ActBook As Workbook
    'display the save as dialog
    varResult = Application.GetSaveAsFilename(InitialFileName:="\\network\folder\upload_" & Format(Date, "yyyy-mm-dd") & ".csv", FileFilter:= _
    "Comma Delimited / Upload Files (*.csv),*.csv", Title:="Save Upload File")
    'check to make sure the user didn't cancel
    If varResult <> False Then
        ActiveWorkbook.saveAs Filename:=varResult, _
        FileFormat:=xlCSV
        Exit Sub
    End If
End Sub

did you loop through the code? which part you are getting error? — Satheesh K, Oct 19 '17 at 15:39
If you take out the second and third line (the `.Select` and `.Activate`) does that help? After you select a sheet, why activate the active workbook? Also note that it's best practice to [avoid using `.Select`/`.Activate` altogether](https://stackoverflow.com/questions/10714251). — BruceWayne, Oct 19 '17 at 15:49

score 0 · Answer 1 · answered Oct 19 '17 at 16:13

0

you can use the sheets defined as workbook/worksheet to avoid issues... may be like this :

Sub saveExportAs()
Dim wb1, wb2 As Workbook
Dim ws As Worksheet
Dim varResult As Variant
Set wb1 = ThisWorkbook
Set ws = ThisWorkbook.Worksheets("load")
ws.Copy
Set wb2 = ActiveWorkbook
varResult =     Application.GetSaveAsFilename(InitialFileName:="\\network\folder\upload_" & Format(Date, "yyyy-mm-dd") & ".csv", FileFilter:= _
"Comma Delimited / Upload Files (*.csv),*.csv", Title:="Save Upload File")
    If varResult <> False Then
        wb2.SaveAs Filename:=varResult, FileFormat:=xlCSV
        wb2.Close Savechanges:=True
        Exit Sub
    End If
wb1.Activate
End Sub

answered Oct 19 '17 at 16:13

Satheesh K

108
9

So after trying this, I get the message: "Path not found: XXXX" where XXXX is just a bunch of cryptic characters, not letters or numbers, but unrecognizable garble. Then run-time error '1004' is thrown; "Method 'Copy' of object '_Worksheet' failed" – payne Oct 19 '17 at 16:34
try using your actual folder path instead of "\\network\folder\upload". If you would still get errors in path selection only, try using the File System Object (FSO) – Satheesh K Oct 19 '17 at 16:42

score 0 · Answer 2 · answered Oct 19 '17 at 16:18

0

Try this...

Sub exportAsCSV()
    Dim wb As Workbook
    Set wb = ActiveWorkbook

    SaveCopyAsCSV ("Sheet1") ' replace Sheet1 with whatever sheet name you need
    wb.Activate

End Sub

Private Function SaveCopyAsCSV(SourceSheet As String)

    Application.DisplayAlerts = False

    ThisWorkbook.Sheets(SourceSheet).copy
    ActiveWorkbook.SaveAs fileName:=SourceSheet, FileFormat:=xlCSV, CreateBackup:=True
    ActiveWorkbook.Close

    Application.DisplayAlerts = True

End Function

answered Oct 19 '17 at 16:18

Maldred

1,074
4
11
33

I thought this was going to work - but I keep getting a failure at the .copy method. I don't know what could keep causing this. – payne Oct 19 '17 at 16:42
run-time error '1004' is thrown; "Method 'Copy' of object '_Worksheet' failed" – payne Oct 19 '17 at 17:30
Did you replace "Sheet1" with the name of yoursheet? – Maldred Oct 19 '17 at 18:08
Can you paste your code in you question that you're using that I provided you? – Maldred Oct 20 '17 at 14:34

VBA Copy Method keeps failing?

2 Answers2