Macros and Saving

[Ant]

Hi there, I am trying to write a macro that will delete columns out of
my current worbook and then save the data that is left as a .txt file.

I'm having trouble when I save the new file as I end up with the txt
file on screen and not the .xls file - which means I cant complete the
recording!

Can anyone help?
Thanks

 

[Dave Peterson]

Open your workbook.
Start recording your macro.
The first step is to copy the worksheet to a new workbook.
(Edit|Move or copy sheet is one way to do this).

Then delete your columns from the activesheet in the activeworkbook.

Then save this activeworkbook (not your original worbook) as a text file.

Your code could look something like this after you clean it up a bit.

Option explicit
sub testme()

dim CurWks as worksheet
dim newwks as worksheet
set curwks = worksheets("sheet99")
curwks.copy 'to a new workbook
set newwks = activesheet

with newwks
.range("a1,c1,f1,h1").entirecolumn.delete
.parent.saveas filename:="c:\test.txt", fileformat:=xltext
.parent.close savechanges:=false
end with

End sub

 

[Ant]

Excellent - thanks very much for the help, works a treat!