Saving sheets as tab delimited text files

[Guest]

I have used the code from http://www.cpearson.com/excel/imptext.htm and
adapted it as below. There are two sheets named BOMIN & BOMINh, and the macro
produces two files, but they are both the same. Whichever sheet is current
when the macro is run is saved twice but with different filenames. How do I
save each sheet?
My code is as below

Public Sub SaveTextFiles()
Dim FName As Variant
Dim Sep As String
Dim m As Integer
Dim sStr As String
Dim MyPath As String
MyPath = "C:\SAPDATA\"

For m = 1 To Sheets.Count
FName = MyPath & Sheets(m).Name
FName = sStr & FName & ".txt"
Sep = vbTab

ExportToTextFile CStr(FName), Sep, False, False
Next m
End Sub

Any help would be appreciated.

 

[Dave Peterson]

The ExportToTextFile subroutine works on the activesheet.

So make sure you change sheets in your code:

For m = 1 To Sheets.Count

sheets(m).select '<--added

FName = MyPath & Sheets(m).Name
FName = sStr & FName & ".txt"
Sep = vbTab

ExportToTextFile CStr(FName), Sep, False, False
Next m

 

[Guest]

Thanks Dave, worked a treat.

The ExportToTextFile subroutine works on the activesheet.

So make sure you change sheets in your code:

For m = 1 To Sheets.Count

sheets(m).select '<--added

FName = MyPath & Sheets(m).Name
FName = sStr & FName & ".txt"
Sep = vbTab

ExportToTextFile CStr(FName), Sep, False, False
Next m