Saving file into a particular path into the current monthly folder

[pscofe]

Hi, I don't use VBA too often so it's very much record and come on here to check other threads! I've written one piece of code which works for what I need to do but now I want it to save into a file path and also in this months folder. What I have so far is:

Sub Formatfile()

Dim myDate As Date

myDate = Application.Max(Columns(1))
ActiveSheet.AutoFilterMode = False
Columns(1).AutoFilter Field:=1, Criteria1:="=" & Format(myDate, [A2].NumberFormat)
Worksheets("Call Tracking Report").Range("B:B,C:C,F:H,K:Q,T:U,AF:AG,AK:AN,AP:AQ").EntireColumn.Hidden = True
Selection.Copy
Range("AR1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Comments"
Range("AR159").Select

End Sub

But I now need it to save down the file automatically (N:\PS\MO 2023\mgmt\investigations\2023). Within this folder i've created all monthly folders.
I've searched for some coding already but doesn't seem to work. Can anyone help? Thanks

 

[OaklandJim]

Unfortunately you have not provided a detailed or clear enough description of your need. It sounds like you want to save a workbook with modifications done by the code that you showed?

First, I suggest adding some comments to your code so it is clear what each line of code is supposed to do.

ActiveSheet.AutoFilterMode = False
Columns(1).AutoFilter Field:=1, Criteria1:="=" & Format(myDate, [A2].NumberFormat)

What is being filtered? Dates?

For example regarding

Selection.Copy

What is selected? It SEEMS that it is the user's selection (selected cells)?

Range("AR159").Select

Apparently, cell AR1 contains the word "Comments." Will comments be copied to another workbook?

Range("AR159").Select

Why do you need to select this cell?

Ideally you provide a link to your workbook so someone willing to assist does not have to 1. recreate your workbook, 2. guess about what is in your workbook and 3. guess about what is needed. Use Box, Dropbox, 1Drive or Sharepoint.