Archive Macro Help

**MattyT92** · 10-04-2024, 08:21 AM

Hello, I am going to try my best to explain this and what I am needing exactly.

I am working on creating a digital log for my job that has a "Day Shift" and "Night Shift" sheet where users will log in daily and input what happens on their shift. The file is in a .xlsm format. There are other sheets on this log but they will all be protected and used just for visual information like important phone numbers and hyperlinks to other stuff inside the company. No user inputs will happen on any other sheets except for the "Day Shift Log" and "Night Shift Log"

I have started working on a macro that will archive the current sheet once a 24 hour time period has lapsed and then clear the unprotected cells.

What I am needing now is code that will save a copy as to a specific "archive" file as read only. I only need the "Day Shift Log" and "Night Shift Log" sheets in the .xlsm file to save to this new file. I do not want to save each sheet as a separate document. They need to be on the same file just in their respective tabs. The save copy file name I need to read as "Frac 6 (current date -1). The reason for the minus 1 is the night shift is the one that will be archiving the log and therefore it will be the following next day. I need it to save as the previous day. Lastly, how can I make this copy "read only" with a password?

After the "save copy as" I need to send these same two sheets only "day shift log" "night shift log" as an email attachment via outlook to my supervisor. If I can make these attachments .pdf that would be ideal.

The rest of the macros I'm pretty much good on but these are the last two I am having trouble with.

**dangelor** · 10-04-2024, 10:48 AM

Saving the 2 worksheets as a pdf...

Sub Save_as_PDF()
    Dim fname As String
    fname = ThisWorkbook.Path & "\Frac 6 (" & Format(Date - 1, "yyyy-mmm-dd") & ")"
    Sheets(Array("Day Shift Log", "Night Shift Log")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname
End Sub

**MattyT92** · 10-04-2024, 10:59 AM

Originally Posted by dangelor

Saving the 2 worksheets as a pdf...

Sub Save_as_PDF()
    Dim fname As String
    fname = ThisWorkbook.Path & "\Frac 6 (" & Format(Date - 1, "yyyy-mmm-dd") & ")"
    Sheets(Array("Day Shift Log", "Night Shift Log")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname
End Sub

Will that just save my current workbook as PDF format? I need the origional log to stay in the same format (.xlsm). I need a copy of the two sheets to save also in .xlsm format just in a diffrent file location with a new name. The email is the only thing I am wanting to convert these two sheets to PDF format.

**dangelor** · 10-04-2024, 07:58 PM

The pdf file is a copy of the worksheets.The original Excel file is unchanged.

Archive Macro Help

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Archive Macro Help

Re: Archive Macro Help

Re: Archive Macro Help

Re: Archive Macro Help

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Archive Macro - Student Data

Excel VBA Archive Monthly Account Returns to an Archive File

[SOLVED] Move row from sheet to archive in a macro

[SOLVED] Teething problems with archive macro

[SOLVED] Macro to replace values in an archive tab

[SOLVED] Excel97 macro to archive Outlook mailbox

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions