Macro that saves a copy of a workbook into a specific location with a specific name

[jalrs]

Hello guys,

Another day, another thread, this time I will be able to explain myself better, hopefully.

So, I have a folder called "prototipo". Inside prototipo I have another folder called "Templates" with all my department templates, and a xlsm file called "STransito" that contains all my Data. (STransito is Located inside prototipo folder if it wasn't clear)

Firstly, I filter the information on "STransito.xlsm" for each department, and send it to the department template, to "Pendentes" sheet, according to this macro: (this example is regarding Apoio SP department)

Option Explicit
Sub ApoioSP()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
 
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("TApoio SP.xlsx")
 
    Set ws1 = wb1.Worksheets("Stock Trânsito")
    Set ws2 = wb2.Worksheets("Pendentes")
 
    ws2.UsedRange.Offset(1).ClearContents
 
    Dim lr1 As Long, lr2 As Long
 
    lr1 = ws1.Cells(Rows.Count, 1).End(3).Row
    lr2 = ws2.Cells(Rows.Count, 1).End(3).Row + 1
 
    With ws1.Range("A5:AV" & lr1)
 
        .AutoFilter 46, "Apoio SP"
        .AutoFilter 47, "Em tratamento"
        .Offset(1).Copy ws2.Cells(lr2, 1)
     
        With ws1.Range("BH6:BH" & lr1)
     
           .Copy ws2.Cells(2, 49)
        
        End With
     
        .AutoFilter
     
    End With
 
    lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    ws2.Range("A" & lr2 & ":A1001").EntireRow.Delete
 
End Sub

Now my next step after running the macro is to send this template file as an attachment via e-mail. Question here is, I don't wan't to save the template, so I don´t lose it. In order to perform this next action I need to save a copy of the template with a new name assigned, in another location. Location would be: C:\Users\joafrodrigue\Desktop\prototipo\Difusao\

Assume the copy name as: "ST_atéDDMMAAAA_Apoio SP" where DDMMAAAA is the current day on that day.

Thanks, hope i can get some help, and let me know if i wasn't clear enough. I'd be more than happy to provide a sample with bogus information if needed

Best Regards,
Afonso

 

[ranman256]

run: MakeCopy

sub MakeCopy()
dim vDir, vSrcFile, vTargFile
vDir = "C:\Users\joafrodrigue\Desktop\prototipo\Difusao\"
vTargFile = vDir & "ST_até" & format(Date(), "DDMMYYYY") & "_Apoio SP.xlsx"
vSrcFile = activeworkbook.FullName
activeworkbook.save
 Copy1File  vSrcFile, vTargFile
end sub


private Function Copy1File(ByVal pvSrc, ByVal pvTarg) As Boolean
Dim fso
On Error GoTo errMake
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile pvSrc, pvTarg
Copy1File = True
Set fso = Nothing
Exit Function
errMake:
MsgBox Err.Description & vbCrLf & pvSrc, , "Copy1File(): " & Err
Set fso = Nothing
End Function

 

[jalrs]

run: MakeCopy

sub MakeCopy()
dim vDir, vSrcFile, vTargFile
vDir = "C:\Users\joafrodrigue\Desktop\prototipo\Difusao\"
vTargFile = vDir & "ST_até" & format(Date(), "DDMMYYYY") & "_Apoio SP.xlsx"
vSrcFile = activeworkbook.FullName
activeworkbook.save
 Copy1File  vSrcFile, vTargFile
end sub


private Function Copy1File(ByVal pvSrc, ByVal pvTarg) As Boolean
Dim fso
On Error GoTo errMake
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile pvSrc, pvTarg
Copy1File = True
Set fso = Nothing
Exit Function
errMake:
MsgBox Err.Description & vbCrLf & pvSrc, , "Copy1File(): " & Err
Set fso = Nothing
End Function

Hey, this is what i got from running your macro. Please see attachment.

Name not complete, plus file type not .xlsx

Thanks!

 

[ranman256]

if your file is not .xlsx, then change the code to: ...Apoio SP.xls"

but it did not create the date part, yet Date() is a standard function.

 

[jalrs]

if your file is not .xlsx, then change the code to: ...Apoio SP.xls"

but it did not create the date part, yet Date() is a standard function.

my source file (TApoio SP.xlsx), where i created a module for the code, is .xlsx as you can see on the attachment.

Regarding the date, instead of ST_atéDDMMAAAA_Apoio SP, can we have "ST_até31032022_Apoio SP" for example?

Thanks