VBA Saving Different Sheet As CSV Than Specified

Question

Trying to save a specific sheet but, VBA is saving other sheets that do not contain the explicitly provided sheet-name. It's supposed to save Sheet-3 (REPORT) but, instead saves Sheet-2 (Instructions).

Sheets found are below:

SYS
Instructions
REPORT
FILE
CHECK
CAMP
CF
AT
RG
NS
OD

Code in question:

Worksheets("REPORT").SaveAs FileName:=PathName & SaveName & "REPORT.csv", FileFormat:=6, CreateBackup:=False
Worksheets(Left(SaveName & "_Report", 31)).Name = "REPORT"

Code is below:

Option Explicit
Sub ACE_MACRO()
Application.DisplayAlerts = False
Dim SaveName As String
Dim PathName As String
Dim CampName As String
Dim Campaign As Integer
SaveName = Right(Date, 2) & Left(Date, 2) & Mid(Date, 4, 2) & Mid(ThisWorkbook.Name, 6, 3) & "_"
PathName = ThisWorkbook.Path & Application.PathSeparator
If 10000 - Application.WorksheetFunction.CountIf(Worksheets("FILE").Range("A2:A10001"), "") = 0 Then
    MsgBox "FILE-sheet is empty. Please add data and try again."
    Exit Sub
End If
Worksheets("REPORT").SaveAs FileName:=PathName & SaveName & "REPORT.csv", FileFormat:=6, CreateBackup:=False
Worksheets(Left(SaveName & "_Report", 31)).Name = "REPORT"

Worksheets("CAMP").Activate
Application.Goto Range("A1")
For Campaign = 1 To (10000 - Application.WorksheetFunction.CountIf(Worksheets("CAMP").Range("B2:B10001"), ""))
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = True
    CampName = Replace(ActiveCell.Offset(0, 1).Value, " ", "-") & "_"
    If (10000 - Application.WorksheetFunction.CountIf(Worksheets("CF").Range("K2:K10001"), "")) = 0 Then
        Else: Worksheets("CF").SaveAs FileName:=PathName & SaveName & CampName & "CF.csv", FileFormat:=6, CreateBackup:=False
        Worksheets(Left(SaveName & CampName & "CF", 31)).Name = "CF"
    End If
    If (10000 - Application.WorksheetFunction.CountIf(Worksheets("AT").Range("K2:K10001"), "")) = 0 Then
        Else: Worksheets("AT").SaveAs FileName:=PathName & SaveName & CampName & "AT.csv", FileFormat:=6, CreateBackup:=False
        Worksheets(Left(SaveName & CampName & "AT", 31)).Name = "AT"
    End If
    If (10000 - Application.WorksheetFunction.CountIf(Worksheets("RG").Range("K2:K10001"), "")) = 0 Then
        Else: Worksheets("RG").SaveAs FileName:=PathName & SaveName & CampName & "RG.csv", FileFormat:=6, CreateBackup:=False
        Worksheets(Left(SaveName & CampName & "RG", 31)).Name = "RG"
    End If
    If (10000 - Application.WorksheetFunction.CountIf(Worksheets("NS").Range("K2:K10001"), "")) = 0 Then
        Else: Worksheets("NS").SaveAs FileName:=PathName & SaveName & CampName & "NS.csv", FileFormat:=6, CreateBackup:=False
        Worksheets(Left(SaveName & CampName & "NS", 31)).Name = "NS"
    End If
    If (10000 - Application.WorksheetFunction.CountIf(Worksheets("OD").Range("K2:K10001"), "")) = 0 Then
        Else: Worksheets("OD").SaveAs FileName:=PathName & SaveName & CampName & "OD.csv", FileFormat:=6, CreateBackup:=False
        Worksheets(Left(SaveName & CampName & "OD", 31)).Name = "OD"
    End If
    Worksheets("CAMP").Activate
    ActiveCell.ClearContents
Next Campaign

Worksheets("CHECK").Range("B2:B10001,E2:E10001,H2:H10001,K2:K10001").ClearContents
Worksheets("FILE").Range("A1:ZZ10001").Clear
Worksheets("Instructions").Activate
ActiveWorkbook.SaveAs FileName:=PathName & "ACEM_" & Mid(ThisWorkbook.Name, 7, 3) & ".xlsm", FileFormat:=52, CreateBackup:=False
MsgBox "Process complete and closing workbook. Have a good day :)"
ActiveWorkbook.Close
End Sub

Before I tackle the main issue, I have a few suggestions: First `Right(Date, 2) & Left(Date, 2) & Mid(Date, 4, 2)` can be rewritten as `Format(Date, "YYMMDD")`. Second `10000 - Application.WorksheetFunction.CountIf(Worksheets("FILE").Range("A2:A10001"), "") = 0` is a roundabout version of `Application.WorksheetFunction.CountIf(Worksheets("FILE").Range("A2:A10001"), "<>") = 0`. Third `Worksheets("CAMP").Activate: Application.Goto Range("A1")` can be written into a single line `Application.Goto Worksheets("CAMP").Range("A1")` — Toddleson, Nov 24 '21 at 22:18
The If statement tree is in a strange situation where the True part is omitted and the Else is the only part that has code. Your structure is `If Then Else End If` which is confusing. If you want the negative case, I suggest changing the expression or using the `Not` keyword. And removing the `Else` from the structure. — Toddleson, Nov 24 '21 at 22:28
`For Campaign = 1 To (10000 - Application.WorksheetFunction.CountIf(Worksheets("CAMP").Range("B2:B10001"), ""))` is looping from 1 to a Count of non-blank cells but doesn't account for their location within the sheet. if there are blank cells in between the data, this loop may not execute over all desired data. — Toddleson, Nov 24 '21 at 22:32
Final comment, Using ActiveCell to track ranges is extremely vulnerable to errors. I highly suggest reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/a/10717999/14608750) which also applies to ActiveCell, ActiveSheet and ActiveWorkbook. — Toddleson, Nov 24 '21 at 22:36
Hi Toddleson. A thousand times appreciated for the information and education. In case you didn't guess, it's pretty much my first rodeo with a "real" macro. I'll definitely take the ActiveCell point into consideration but, for this project, I'm not concerned since that's the record ID and it would be a concern of its own if there were blank rows. In regards to the weird If-statement, I'll definitely fix it to leverage the Not keyword but, for my own education, are there risks associated with keeping it If-Then-Else or is it just best practice? — abrakadabra, Nov 29 '21 at 20:59
If it runs, it runs. But when it comes time to edit or expand the macro, you'll have a hard time working with code that isn't in an understandable format. Its even more of a pain when trying to pass on the code to another person, like posting on SO for help. It doubles the work required by person trying to read & edit the code, as they have to wrap their head around your unique structure without the experience of being the author. Working with popular formats also means you're better positioned to copy other people's solutions (Like an answer from this website) into your project. — Toddleson, Nov 29 '21 at 21:21

Toddleson · Answer 1 · 2021-11-24T23:02:08.737

1

I don't see any possibility of your code saving the Instruction worksheet. Your line Worksheets("REPORT").SaveAs is correct and as long as a sheet with that name exists, your code will export it as csv.

Since the sheet names are being changed and renamed, to help ensure that the sheet name is preserved after the .SaveAs, you can use a With statement to save the object while it is being changed. This will ensure that sheet names are extra reliable as identification.

With Worksheets("REPORT")
    .SaveAs Filename:=PathName & SaveName & "REPORT.csv", FileFormat:=6, CreateBackup:=False
    .Name = "REPORT"
End With

edited Nov 24 '21 at 23:02

answered Nov 24 '21 at 22:40

Toddleson

4,321
1
6
26

Agreed. Something must be happening somewhere else in OP code. To prove it, strip everything out except this line of code, and it will save the proper methd: `Worksheets("REPORT").SaveAs Filename:=pathName & SaveName & "REPORT.csv", FileFormat:=6, CreateBackup:=False` – pgSystemTester Nov 25 '21 at 00:51
Hey, so. I made all the edits as suggested and tried running it to only save the REPORT sheet. Now I'm getting the all-too-fun '1004' runtime error for 'Application-defined or object-defined' error so, I can't even test the changes you suggested. It's weird because it was working last week when I cam to SO for help. I'm on a Mac but, it seems this issue plagues Windows as well. Do any of you have any suggestions here (assume same code with only the suggested edits)? – abrakadabra Nov 29 '21 at 23:30
@abrakadabra Without seeing the changes, I wouldn't be able to guess what would be causing the error. I suggest you make a new post describing the issue. – Toddleson Nov 30 '21 at 14:31

VBA Saving Different Sheet As CSV Than Specified

1 Answers1