Macro to Export Sheet

[howard]

I have the following code to export/copy a sheet into a specific folder


I would like the code amended so that if C2 is zero or blank on Sheets("BR1 JNL")then the sheet will not be copied to the folder , then the sheet will not be copied


It would be appreciated if someone could kindly amend my code to do the above

 Sub Br1JNLasCSV()

Sheets("BR1 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:\Journals\BR1 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

 

[GWteB]

How about ...

Sub Br1JNLasCSV()


    Dim vTemp As Variant
    Dim lr As Long
    Dim lC As Long
    Dim i As Long
    Dim indexColumn As Long
    Dim indexRow As Long


    With ThisWorkbook.Sheets("BR1 JNL")

        If .Range("C2").Value = 0 Then Exit Sub
        If Len(Trim$(.Range("C2").Value)) = 0 Then Exit Sub

        ' 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:\Journals\BR1 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 With
End Sub

 

[howard]

many thanks for the help. code works perfectly

 

[GWteB]

Glad to help & thanks for letting me know