Save as .txt without activating

  • Thread starter Thread starter lud1
  • Start date Start date
L

lud1

Hi,
The problem is following.
I have a file with two sheets (A & B). How to create macro what after
clicking save button on the toolbar, saves the file
1. as .xls file with name from f3 cell (A) with activate it
2. as .txt file in different directory without activate it. The only
sheet what should be saved is B.

Thanks for quick reply

lud1
 
How about giving them a macro that does the save and saveAs all at once.

Option Explicit
Sub testme()

Dim Wks As Worksheet

Set Wks = Nothing
On Error Resume Next
Set Wks = ThisWorkbook.Worksheets("b")
On Error GoTo 0

If Wks Is Nothing Then
MsgBox "worksheets B not found" & vbLf & "Not Saved!"
Exit Sub
End If

With ThisWorkbook
.SaveAs Filename:=.Path & "\" _
& .Worksheets("a").Range("F3").Value & ".xls", _
FileFormat:=xlNormal

Wks.Copy 'to new workbook
ActiveWorkbook.SaveAs Filename:=.Path & "\" & "somenamehere.txt", _
FileFormat:=xlTextMSDOS
ActiveWorkbook.Close savechanges:=False
End With

End Sub

(not very much error checking with the value in F3 and you didn't say what to
name the .txt file.
 
Back
Top