Solved

Excel Macro - skip sheet if not present?

Posted on 2013-11-25
12
450 Views
Last Modified: 2013-11-26
Each week I get an Excel workbook containing cigarette pricing for the following week.  I have to save a number of sheets in this workbook (but not all of them) off to CSV files so that they can be imported into our point of sale systems. I used the macro recorder to record the process, and it works great so long as none of the specified sheets are missing.  How can I manually edit this macro to tell Excel to skip any sheets that it cannot find?

Sub CigPackSave()
'
' CigPackSave Macro
' Macro to save cigarette worksheets as individual CSV Files.
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    ChDir "C:\Users\jplemons\Desktop\temp"
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\cigpack.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG29 G IN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG29.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG28 G IL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG28.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG27 G OH").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG27.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG26 G VA").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG26.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG25 G TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG25.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG24 G TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG24.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG23 G TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG23.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG22 G TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG22.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG21 C TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG21.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG20 G AL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG20.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG19 C IN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG19.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG18 G AL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG18.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG17 G AL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG17.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG16 G IL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG16.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG15 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG15.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG14 G AL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG14.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG13 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG13.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG12 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG12.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG11 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG11.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG10 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG10.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG09 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG09.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG08 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG08.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG07 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG07.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG06 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG06.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG05 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG05.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG04 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG04.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG03 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG03.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG02 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG02.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG01 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG01.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.ScrollWorkbookTabs Sheets:=17
End Sub
0
Comment
Question by:btkrausen
  • 7
  • 5
12 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39674892
Fairly simple workaround, at the beginning of the code put this line:

On Error Resume Next

This will basically ignore any error and continue to the next step of the script.

However, downside being it will ignore ALL errors.

Thanks,
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39674902
Also, could be simplified considerably by creating a loop rather than repeating for each and every sheet.

Thanks
Rob H
0
 

Author Comment

by:btkrausen
ID: 39674906
Honestly, as simple as this process is, that might not be a bad option.  I'm going to give it a try.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39674940
Simplified to

Sub CigPackSave()

DestDir = "C:\Users\jplemons\Desktop\temp\cigpack"

For Each Worksheet In ActiveWorkbook.Worksheets

    ShtName = Worksheet.Name
    Sheets(ShtName).Copy
    NewFile = Left(ShtName, 4) & ".csv"
    ActiveWorkbook.SaveAs Filename:=DestDir & NewFile _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close

Next Worksheet

End Sub

Open in new window

It looks like you do something different for the first sheet, the one that gets named cigpack.csv

What is the name of this sheet so it can be dealt with as an exception?

Thanks
Rob H
0
 

Author Comment

by:btkrausen
ID: 39674964
So, the macro will run with that line in it, but there's a side effect.  The macro still creates .csv files for all named sheets and those csv files contain data copied, presumably, from one of the other tabs.  I'm not sure which one at this point, but I can probably take some time later today to track it down. Does that make sense?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39675040
It will be data from the previous correctly selected sheet.

The Error handler is merely skipping the line that Selects the sheet, the next line is still saving the current sheet as a csv with the name specified.

Strictly speaking an error handling routine would normally be set to ignore the Save As step as well.

Have you tried the condensed version?

Thanks
Rob H
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:btkrausen
ID: 39675114
Rob,

I hadn't refreshed my screen in a while, so I missed the condensed version - sorry.  I tried the condensed version and it worked really well with one minor change - I set the destination directory to be simply: C:\Users\jplemons\Desktop\temp\

The first worksheet in my original macro is named "PROMOTIONS" and then saved as cigpack.csv.

How can I exclude the sheets named "INDEX" and "NOTES"?  Is there any way to make it so that I don't have to click save after every one?

Thanks!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39675453
Shouldn't be a problem to miss specific sheets and switching off event warning should handle the messages.
0
 

Author Comment

by:btkrausen
ID: 39675829
I was able to switch off the warnings and write the exceptions for the specific tabs that I wanted skipped - see code below.  Can you assist with the renaming of the PROMOTIONS sheet to cigpack.csv?

Sub CigPackSave()

DestDir = "C:\Users\jplemons\Desktop\temp\"

Application.DisplayAlerts = False

For Each Worksheet In ActiveWorkbook.Worksheets
  If Worksheet.Name <> "INDEX" And Worksheet.Name <> "BASE PRICING" And Worksheet.Name <> "DISCOUNTING" And Worksheet.Name <> "PRICING SUMMARY" And Worksheet.Name <> "PRINT PAGE" And Worksheet.Name <> "NOTES" Then
    ShtName = Worksheet.Name
    Sheets(ShtName).Copy
    NewFile = Left(ShtName, 4) & ".csv"
    ActiveWorkbook.SaveAs Filename:=DestDir & NewFile _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
  End If
Next Worksheet

End Sub

Open in new window

0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39676245
Try this:

Sub CigPackSave()

DestDir = "C:\Users\jplemons\Desktop\temp\"

Application.DisplayAlerts = False

For Each Worksheet In ActiveWorkbook.Worksheets
    ShtName = Worksheet.Name
    Select Case ShtName
    Case "INDEX", "BASE PRICING", "DISCOUNTING", "PRICING SUMMARY", "PRINT PAGE", "NOTES"
    GoSub Ignore

    Case "PROMOTIONS"
    GoSub Promotions
    
    Case Else
    GoSub CreateCSV
    
    End Select

Next Worksheet

Application.DisplayAlerts = True

Exit Sub
Ignore:
    Return

Promotions:
    Sheets(ShtName).Copy
    NewFile = "cigpack.csv"
    ActiveWorkbook.SaveAs Filename:=DestDir & NewFile _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    Return
    
CreateCSV:
    Sheets(ShtName).Copy
    NewFile = Left(ShtName, 4) & ".csv"
    ActiveWorkbook.SaveAs Filename:=DestDir & NewFile _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    Return
End Sub

'  If Worksheet.Name <> "INDEX" And _
'  Worksheet.Name <> "BASE PRICING" And _
'  Worksheet.Name <> "DISCOUNTING" And _
'  Worksheet.Name <> "PRICING SUMMARY" And _
'  Worksheet.Name <> "PRINT PAGE" And _
'  Worksheet.Name <> "NOTES" Then CreateCSV

Open in new window


This assesses the sheet name and uses Select Case options to determine what to do. I have split the three options into 3 subroutines:

Ignore - This Case is hard coded for specific sheet names, to ignore more just add them on the end comma separated and enclosed in double quotes.

Promotions - Deals with specific Case

CreateCSV - Deals with everything else.

Might have been better to deal with CG sheets as specific cases eg Left(ShtName,2) = "CG", any sheets not covered by above can then be flagged if so required rather than falling into the "everything else" bucket.

I have also added the line to switch back on Display Alerts.

The chunk of commented out text at the bottom of the script was another alternative but using line separators to ease the reading.

I haven't tested any of this as I don't have the file with multiple sheets.

Thanks
Rob H
0
 

Author Closing Comment

by:btkrausen
ID: 39677465
Rob,

That worked perfectly!  Thank you for providing me with some of the logic behind your code - it has really helped me better understand what's going on.  Will make future macros a little easier.

Thanks!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39677491
Created Test File, attached.

Note change to first two lines of script setting Destination Directory of CSV files. I have commented out your specific directory and set it to save in a subfolder of the Active Directory called CSV. To test in your scenario you will have to create the CSV folder.

This slight modification to script above checks sheet name for specifics to ignore or treat differently and then for naming convention starting with CG.

If any additional sheets are found it will generate an error message at the end listing those sheets otherwise it gives a confirmation message for all sheets processed.

I also switched off Screen Updating so you don't have the flashing between sheets as the files are created.

By switching off DisplayAlerts the csv files will automatically overwrite existing files.

Thanks
Rob H
Create-CSVs.xlsm
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now