VBA - Excel tab to CSV - Help

jimmyaja123

New Member
Joined
Jan 6, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

 
Last edited by a moderator:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

EFANYoutube

Active Member
Joined
May 19, 2017
Messages
278
Hi,
Can you try the below?
VBA Code:
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

Active Member
Joined
May 19, 2017
Messages
278
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
VBA Code:
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

New Member
Joined
Jan 6, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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!
 

Forum statistics

Threads
1,141,777
Messages
5,708,461
Members
421,571
Latest member
ChaosPup

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top