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

ChanL

New Member
Joined
Apr 8, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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:
VBA Code:
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
Try
FileFormat:=xlTextWindows
or
FileFormat:=20

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,187
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Does this solve it
VBA Code:
Sheets("Main").Cells(3, 2).Value & "799EBS.txt", FileFormat:=20
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,176
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 8, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
Does this solve it
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
Sheets("sheet1").Activate
'your save command here
 

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,640
Members
417,159
Latest member
Mayozero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top