Results 1 to 2 of 2
  1. #1
    Lurker
    Join Date
    Aug 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export multiple (specific) worksheets to CSV files in a specified directory

    I'm new to VBA. I'm trying to do the following but the code isn't quite working as expected -

    1. Export/Copy particular sheets in the workbook (any sheet name that contains "Upload") to a particular file directory.
    2. I don't want these worksheet names to change nor the workbook name to change.
    3. The file-name is consistent for each worksheet, so it would be okay to replace the files in the directory whenever I run the macro. Its okay to have a dialog box that asks if I'm sure I want to replace each of the files.
    4. I don't want the newly created CSVs or any other file to open.

    --
    Sub COPYSelectedSheetsToCSV()
    '
    '
    Sheets("Moo Upload").Select
    Sheets("Moo Upload").Name = "Moo Upload"
    ActiveWorkbook.SaveAs Filename:="/Users/reginaho/Desktop/Upload/Moo Upload.csv", _
    FileFormat:=xlCSV, CreateBackup:=False
    Sheets("Dodo Upload").Select
    ActiveWorkbook.SaveAs Filename:="/Users/reginaho/Desktop/Upload/Dodo Upload.csv", _
    FileFormat:=xlCSV, CreateBackup:=False
    Sheets("Lulu Upload").Select
    ActiveWorkbook.SaveAs Filename:="/Users/reginaho/Desktop/Upload/Lulu Upload.csv", _
    FileFormat:=xlCSV, CreateBackup:=False
    Sheets("Ahhh Upload").Select
    ActiveWorkbook.SaveAs Filename:="/Users/reginaho/Desktop/Upload/Ahhh Upload.csv", _
    FileFormat:=xlCSV, CreateBackup:=False
    End Sub

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    reghyh,

    The following code will copy each sheet of the main workbook only if the sheet's name contains "upload" (case insensitive) to a CSV file with the same name. Please change the path to your destination folder. Alert messages to overwrite an existing file of the same name will be suppressed.

    HTH,
    Maud

    Place in a standard module:
    Code:
    Sub CreateCSV()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    '-----------------------------
    'DECLARE AND SET VARIABLES
    Dim wb1 As Workbook, ws1 As Worksheet
    Dim wbname As String, I As Integer
    Set wb1 = ThisWorkbook
    '-----------------------------
    'CYCLE THROUGH SHEETS AND MATCH UPLOAD
    For I = 1 To Worksheets.Count
        wbname = Worksheets(I).Name
        If InStr(1, Ucase(Worksheets(I).Name), "UPLOAD", vbTextCompare) > 0 Then
    '-----------------------------
    'COPY SHEET INTO NEW CSV FILE
            Worksheets(I).Copy
            ActiveWorkbook.SaveAs Filename:="C:\Users\Maudibe\Documents\" & wbname & ".csv", _
            FileFormat:=xlCSV, CreateBackup:=False
            ActiveWorkbook.Close
            wb1.Activate
        End If
    Next I
    '-----------------------------
    'CLEANUP
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    Last edited by Maudibe; 2015-08-22 at 02:32.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •