VBA Macro Not 'Saving As'

Question

I have a macro which applies conditional formatting and filters. The macro should filter the file and save this out, it should then remove filters and filter again using different columns and save these results to another file. The macro is running but the files don't seem to be saving?

Code

Sub Customer_Connections()
'
' Macro1 Macro
'
Application.ScreenUpdating = False
' Email Must Be In Column F

'Duplicate Email
Columns("F:F").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
    .Color = -16383844
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13551615
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$Z$999999").AutoFilter Field:=6, Criteria1:=RGB(255, _
    199, 206), Operator:=xlFilterCellColor

   ActiveWorkbook.SaveCopyAs ("Duplicate_Emails-" & Format(Now(), "ddmmyyyy") & ".xlsm")

'More Than One @
With Sheets("Customer Connections")

.Columns("G:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("G:G").NumberFormat = "General"

Cells.Select
Selection.AutoFilter
With .Range("G2:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
    .Formula = "=LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],""@"",""""))"
    .Copy
    .PasteSpecial xlPasteValues
End With
Range("G1").Select
ActiveCell.FormulaR1C1 = "Count Of @"

Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$L$999999").AutoFilter Field:=7, Criteria1:="2"


End With

  ActiveWorkbook.SaveCopyAs ("Two_@_In_Emails-" & Format(Now(), "mmddyyyy") & ".xlsm")

End Sub

You should write out the destination file path for your new files, like "C:\Users\Me\Desktop\", etc. — dwirony, Mar 04 '19 at 15:57
I'd prefer to have it output to the same folder that my report is in. It was working initially but seems to have stopped as I built out the macro — Eoin2211, Mar 04 '19 at 15:59
I can't reproduce your issue, your code should work fine... Are you sure they aren't saving in your `ActiveWorkbook`'s folder? — dwirony, Mar 04 '19 at 16:03
Is there a way to print the active workbook location? The're not appearing in the folder alongside the macro file — Eoin2211, Mar 04 '19 at 16:14
Check this: https://stackoverflow.com/questions/2813925/how-to-get-the-path-of-current-worksheet-in-vba — Ricardo Diaz, Mar 04 '19 at 16:18
@E_McAndrew Try `Debug.Print ActiveWorkbook.Path` at the start, that will print to the immediate window. Or use `MsgBox ActiveWorkbook.Path`. — dwirony, Mar 04 '19 at 16:24
@E_McAndrew Like I said, I can't reproduce your issue. You need to step through your code using `F8` and go line by line - when you hit the save line, see if anything shows up in the folder. Or try moving the workbook to your desktop and running the code, then see if the files are saved on your desktop. — dwirony, Mar 04 '19 at 18:14

score 1 · Accepted Answer · answered Mar 04 '19 at 20:56

1

Try this:

ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & _
                    "\Two_@_In_Emails-" & Format(Now(), "mmddyyyy") & ".xlsm"

answered Mar 04 '19 at 20:56

Tim Williams

154,628
8
97
125

VBA Macro Not 'Saving As'

1 Answers1