How to solve run-time error '1004' : select method of range class failed

[ChanL]

I have run a series of VBA code ( copy paste data from one sheet to another by match the column name, format the sheet) and now I need to save the sheet as text file.

This is the code I use:

Sub savefile()

Sheets("Sheet1").Range("A1").Select
ChDir sheets("Main").Cells(3,2).value
ActiveWorkbook.SavesAs filename:= _
Sheets("Main").Cells(3,2).value+"799EBS.txt",fileformat:=xltext, _
create backup=false

end sub

 

[Zot]

I guess your file format is not valid. Refer here and use either name or enumeration

XlFileFormat enumeration (Excel)

Office VBA reference topic

docs.microsoft.com

 

[ChanL]

Thanks but i think .txt already is the correct name ?

I guess your file format is not valid. Refer here and use either name or enumeration

XlFileFormat enumeration (Excel)

Office VBA reference topic

docs.microsoft.com

 

[Zot]

Try
FileFormat:=xlTextWindows
or
FileFormat:=20

xlText not in the list. Perhaps valid on Excel 2003 I'm not sure. Cannot recall.

 

[Michael M]

Does this solve it

Sheets("Main").Cells(3, 2).Value & "799EBS.txt", FileFormat:=20

 

[Zot]

When Excel file extension was only .xls, you can get away just by naming the file with extension. Now you need to specify FileFormat

 

[ChanL]

Does this solve it

Sheets("Main").Cells(3, 2).Value & "799EBS.txt", FileFormat:=20

Hi, still hit error. My main problem is that because currently my workbook got three sheets, but I only want to save "sheet1" as txt files. But before this I actually didn't add "Sheets("Sheet1")" in front of ".Range("A1").Select". But then the code always save the another sheet instead of "sheet1", that's why I try to specify "sheet1" in front of it. But then hit an error.

Or if by any chance, do you have any suggestions on how I can achieve this ? Like how can I modify my codes?

 

[Joe4]

Activate the sheet you wish to save in the line of VBA code JUST before your save command, as when saving to a text file, Excel will always save whatever is the active sheet, i.e.

Sheets("sheet1").Activate
'your save command here