I am seeking help to write a code that will export variable ranges in each worksheet of a workbook to separate tab-delimited text files that have the same filename but are appended with the name of the worksheet they came from (e.g., \filename_03.txt, \filename_04.txt, ...). For example, I want data starting from I6 down to the last cell of data in column K. Columns I:K will be consistent, but the number of rows will not be the same for each worksheet. Column I has formulas for I6:I60000, so Range selection in code should probably select from K6 down, then over two columns to the left. I've tried writing this, but keep getting a error (see "ExportAll" code below). The range should then be exported to a tab-delimited text file. This should then be repeated for all but the last three worksheets.
Code should do the following:
-select range I6:K#, where # = number of rows from K6 thru the last occupied cell in K
-output range to text file with specified filename appended with "_" & worksheet name
-be prompted for filename and save location, ideally from same location as workbook
-repeat for each consecutive worksheet, except for last three ("all","data","summary")
-should take same filename indicated previously, just with a new appended name
I have a code that will do this process for one worksheet at a time, but is rather cumbersome for multiple worksheets. (I found these macros on the Web: http://www.cpearson.com/excel/ImpText.aspx).
I've also tried using the code below to process multiple worksheets, but I get an error on Line 28
Any suggestions on how to do this properly?
Bookmarks