VBA - Excel tab to CSV - Help

[jimmyaja123]

Hello,

I was hoping someone could assist in the VBA code i've written or help simplify it.

What it does is that it looks at another tab and creates a CSV file from it and puts the file in a folder location.

The problem i'm having is that when the csv file is made it takes all the data from the tab Load, whereas i want everything in the load tab except column A to be in the csv file.

I've tried to change the ranges to B but it still pulls in everything from the tab including column A.

Can someone help?

Sub CSVOutput()

If Sheets("Notes").Range("d16") = 0 Then

Sheets("Load").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AH$600").AutoFilter Field:=1, Criteria1:="="
Rows("369:369").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$AH$368").AutoFilter Field:=1
Selection.AutoFilter

Dim myCSVFileName As String
Dim tempWB As Workbook
Path1 = Worksheets("Notes").Range("C9")

Application.DisplayAlerts = False
On Error GoTo err

myCSVFileName = Worksheets("Notes").Range("C10")

ThisWorkbook.Sheets("Load").Activate
ActiveSheet.Copy
Set tempWB = ActiveWorkbook

With tempWB
.SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
err:
Application.DisplayAlerts = False

Sheets("Load").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range("A2:M4000").Select
ActiveSheet.Paste
ActiveWorkbook.Save
Sheets("Notes").Select
Range("A1").Select

Else

Excel file to CSV help

Hello, I was hoping someone could assist in the VBA code i've written or help simplify it. What it does is that it looks at another tab and creates a CSV file from it and puts the file in a folder location. The problem i'm having is that when the csv file is made it takes all the data...

www.excelforum.com

 

[EFANYoutube]

Hi,
Can you try the below?

Sub CSVOutput()

If Sheets("Notes").Range("d16") = 0 Then

Sheets("Load").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AH$600").AutoFilter Field:=1, Criteria1:="="
Rows("369:369").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$AH$368").AutoFilter Field:=1
Selection.AutoFilter

Dim myCSVFileName As String
Dim tempWB As Workbook
Path1 = Worksheets("Notes").Range("C9")

Application.DisplayAlerts = False
On Error GoTo err

myCSVFileName = Worksheets("Notes").Range("C10")

ThisWorkbook.Sheets("Load").Activate
ActiveSheet.Range("B1", ActiveSheet.Range("B1").End(xlToRight).End(xlDown)).Copy
Set tempWB = ActiveWorkbook

With tempWB
.SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
err:
Application.DisplayAlerts = False

Sheets("Load").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range("A2:M4000").Select
ActiveSheet.Paste
ActiveWorkbook.Save
Sheets("Notes").Select
Range("A1").Select

Else

 

[EFANYoutube]

For a little more compact code try the below. Please try this on a copy of your workbook as there may be things I don't know about your workbook that may have adverse side affects

Sub CSVOutput()

If Sheets("Notes").Range("d16") = 0 Then

    With Sheets("Load")
        Range(("A2"), .Range("B1").End(xlToRight).End(xlDown)).AutoFilter Field:=1, Criteria1:="="
        Range(("A369"), .Range("A369").End(xlDown)).EntireRow.Delete xlUp
        Range("A1").AutoFilter
    
        Dim myCSVFileName As String
        Dim tempWB As Workbook
        Path1 = Worksheets("Notes").Range("C9")
        
        Application.DisplayAlerts = False
        On Error GoTo err
        
        myCSVFileName = Worksheets("Notes").Range("C10")
    
        .Range("B1", .Range("B1").End(xlToRight).End(xlDown)).Copy
        Set tempWB = ActiveWorkbook
    
        With tempWB
            .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
            .Close
        End With
err:
        Application.DisplayAlerts = False
        
        .Range("A2", .Range("A2").End(xlToRight)).Copy .Range("A2:A4000")
        ActiveWorkbook.Save
        Sheets("Notes").Select
        Range("A1").Select

    End With
End If

End Sub

 

[jimmyaja123]

For a little more compact code try the below. Please try this on a copy of your workbook as there may be things I don't know about your workbook that may have adverse side affects

Sub CSVOutput()

If Sheets("Notes").Range("d16") = 0 Then

    With Sheets("Load")
        Range(("A2"), .Range("B1").End(xlToRight).End(xlDown)).AutoFilter Field:=1, Criteria1:="="
        Range(("A369"), .Range("A369").End(xlDown)).EntireRow.Delete xlUp
        Range("A1").AutoFilter
   
        Dim myCSVFileName As String
        Dim tempWB As Workbook
        Path1 = Worksheets("Notes").Range("C9")
       
        Application.DisplayAlerts = False
        On Error GoTo err
       
        myCSVFileName = Worksheets("Notes").Range("C10")
   
        .Range("B1", .Range("B1").End(xlToRight).End(xlDown)).Copy
        Set tempWB = ActiveWorkbook
   
        With tempWB
            .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
            .Close
        End With
err:
        Application.DisplayAlerts = False
       
        .Range("A2", .Range("A2").End(xlToRight)).Copy .Range("A2:A4000")
        ActiveWorkbook.Save
        Sheets("Notes").Select
        Range("A1").Select

    End With
End If

End Sub

Thank you!