Simple VB error

[gaz_chops]

Recorded a simple macro to copy & paste from 1 sheet to a new sheet, then I added code I found here to save as a CSV file using the name from a cell.

It fails on
FileFormat:=xlCSV

Sub XeroCSV()
'
' XeroCSV Macro
'

'
    Dim FileName As String
    Dim Path As String
    Sheets("Xero Import").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    Path = "H:\Documents\Xero Reports\IRIS Payroll Data for Xero\"
    FileName = Range("G2").Value & ".csv"
    ActiveWorkbook.SaveAs Path & FileName
    FileFormat:=xlCSV
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Sheets("Journal").Select
    Range("G5").Select
End Sub

I am a complete beginner with VB, so any help appreciated.

Thanks
Gaz

 

[Fluff]

How about

Sub XeroCSV()
'
' XeroCSV Macro
'

'
    Dim FileName As String
    Dim Path As String
    Sheets("Xero Import").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    Path = "H:\Documents\Xero Reports\IRIS Payroll Data for Xero\"
    FileName = Range("G2").Value & ".csv"
    ActiveWorkbook.SaveAs Path & FileName, xlCSV
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Sheets("Journal").Select
    Range("G5").Select
End Sub

 

[gaz_chops]

How about

Sub XeroCSV()
'
' XeroCSV Macro
'

'
    Dim FileName As String
    Dim Path As String
    Sheets("Xero Import").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    Path = "H:\Documents\Xero Reports\IRIS Payroll Data for Xero\"
    FileName = Range("G2").Value & ".csv"
    ActiveWorkbook.SaveAs Path & FileName, xlCSV
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Sheets("Journal").Select
    Range("G5").Select
End Sub

Thanks Fluff, it fails here

ActiveWorkbook.SaveAs Path & FileName, xlCSV

 

[Fluff]

What is the error message & number?

 

[gaz_chops]

Run-time error '1004'

Method 'SaveAs' of object '_workbook' failed

 

[Fluff]

What is the value in G2?

 

[gaz_chops]

G2 contains a formula =TEXT(B2,"mmm")&" Payroll" which shows in G2 as May Payroll

 

[gaz_chops]

What is the value in G2?

Fluff apologies,
I realise that I had the bit of code that creates filename in the wrong place, so it was looking at G2 on the newly created sheet!
I moved that bit of code to near the top and it now seems to work ok

Many thanks

Gaz

 

[Fluff]

Ok, are you sure that the filepath is correct & that you have write permissions?

 

[gaz_chops]

Ok, are you sure that the filepath is correct & that you have write permissions?

Yes all is working now, when you asked what is in G2 I realised what i'd done.

Cheers
Gaz