I have an excel doc with 7 (a-g)columns data(data is formulas if that matters), each column has a header (10 rows from cell 1-10) and is 45 rows deep of data (cells 20-65) and after the 45 rows there are 2 rows of formatting i need to enter. I need a seperate TXT file for each column (or set of 45 rows).
I need a macro that will look in each of the rows and first see if there is data, so it will only export if there is data to export. then it needs to know where the data ends and in which column as each column needs to export to its own TXT file.
Finally, I need it to say end and end file in the last two rows 46 and 47 in the txt file for each file.
currently im using this code, but it isnt working:
If anyone has any insight it would help alot
I need a macro that will look in each of the rows and first see if there is data, so it will only export if there is data to export. then it needs to know where the data ends and in which column as each column needs to export to its own TXT file.
Finally, I need it to say end and end file in the last two rows 46 and 47 in the txt file for each file.
currently im using this code, but it isnt working:
Code:
Option Explicit
Sub SaveColumnsToText()
'Save each column to separate text file
Dim LR As Long, dCol As Long, Cnt As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
dCol = 1: Cnt = 1
Do
If Application.WorksheetFunction.CountA(Columns(dCol)) Then
Columns(dCol).Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Range("A" & Rows.count).End(xlUp).Offset(1, 0) = "END-OF-DATA"
Range("A" & Rows.count).End(xlUp).Offset(1, 0) = "END-OF-FILE"
ActiveWorkbook.SaveAs Filename:="filename", FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close False
dCol = dCol + 1
Cnt = Cnt
Else
Exit Do
End If
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End SubIf anyone has any insight it would help alot
