Hi Patel45
I have a set up a seperate macro for each JNL sheet name to export the sheet as a CSV file and then one Macro which exports all the sheet names with the name JNL as a CSV file
Your assistance in this regard is most appreciated
See code below
Sub Br1JNLasCSV()
Sheets("Br1 Int JNL").Select
Dim vTemp As Variant
Dim lr As Long
Dim lC As Long
Dim i As Long
Dim indexColumn As Long
Dim indexRow As Long
' get depth of rows from column A
lr = Cells(Rows.Count, 1).End(xlUp).Row
'get width of data from row 1 (header)
lC = Cells(1, Columns.Count).End(xlToLeft).Column
'open the file as output
Open "c:\Journal Templates\Br1 Int JNL.csv" For Output As #1
'move throught the spreadsheet, 1 row down all columns across
For indexRow = 1 To lr Step 1
For indexColumn = 1 To lC Step 1
'remove " from cells
vTemp = Trim(Replace(Cells(indexRow, indexColumn), Chr(34), "'"))
'Print headers so they are clear MyAddess1 rather than "Myaddress1"
If (indexRow = 1) Then
If (indexColumn = lC) Then
Print #1, vTemp ' end of line
Else
Print #1, vTemp & ","; ' comma sep (required for the print statement
End If
Else
If indexColumn = lC Then
Write #1, vTemp 'end of line using write
Else
Write #1, vTemp; 'continuation of line using write
End If
End If
Next indexColumn
Next indexRow
Close #1
End Sub
Sub JNL_export_CSV()
Br1JNLasCSV
Br2KNLasCSV
Br3JNLasCSV
Br4JNLasCSV
Br5JNLasCSV
Br6JNLasCSV
Br7JNLasCSV
End Sub
