Saving data selection as tab seperated txt file

First post here, be nice

Third post here, actually: https://www.excelforum.com/excel-gen...t-filters.html

Are you still using Excel 2010? If not, please update your profile accordingly. Thanks.

Would you also clarify. Are you copying columns A:C from one sheet onto the "For Keynote Export" sheet and then wanting to save just that keynote export sheet as a single sheet additional workbook file?

If so what name are you trying to give to the saved file? At the moment it's being given the same name as the file with which you start.

Perhaps the following.

The assumption is that the stuff you want to copy to the Keynote export sheet can be from any other sheet. Hence the code sets a reference to the sheet with the Set wsInitialSheetName syntax. Of course if it's always copied from the same sheet then there's no need for that.

You could simply replace the
wsInitialSheet.Columns("A:C").Copy Sheet2.Range("A1")

with the VBA sheet code name - let's assume that's Sheet1. Hence the line of code would be

Sheet1.Columns("A:C").Copy Sheet2.Range("A1")

Note that I can't see any need for the SaveAs at the end unless you really do want to change the file name so I've commented it out.

Finally, rarely is it necessary to use code like .Select or .Activate. That just slows macros down, particularly when you may be using a loop to do the same stuff many times. Just reference objects directly. In addition it's regarded as good practice to put all Dim statements at the top of any code and always define the type of variable with the 'As ...whatever'. e.g. As String, As Long, As Workbook...

Please Login or Register  to view this content.

Hi,

It's the line
wsInitialSheet.Columns("A:C").Copy Sheet2.Range("A1")

that copies the stuff to the Keynote sheet in one action, unlike a Copy (into memory) and then Paste as your original was doing. AT that point there is nothing in memory.

Similarly the SHeet2.Copy is a single action, and thinking a bit more about this the CutCopyMode = False isn't required since there is nothing in memory either

Yes you are correct. At the end you are still in the original workbook. I left the Save in since you may feel you want an up to date copy that represents the precise position when you saved the .txt file. You can leave out the Save if you wish.

You're also correct that it's generally good policy to reverse an instruction like Display Alerts as soon as possible unless there are reasons not to. The same thinking applies with stuff like On Error REsume Next....On Error Goto 0, Application.Calculation = xlCalculationManual... Application.Calculation =xlAutomatic, ...ScreeUpdating = False....ScreenUpdating = True, and no doubt several others

So I got some help to make the file portable by switching from ThisWorkbook to ActiveWorkbook. But now it's not working again and stops at wsInitialSheet.Columns("A:C").Copy Sheet2.Range("A1")

Please Login or Register  to view this content.