VBA Copy Method keeps failing?

0
votes

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
2
vbaexcel
did you loop through the code? which part you are getting error? – Satheesh K
i have, and it hangs at Sheets("load").Copy – payne
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. – BruceWayne

2 Answers

0
votes

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
0
votes

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