MACRO to Protect Worksheet and Autosave File

[chrono2483]

Hello Everyone,

Did some additional research and got some help on a code to protect my worksheet, as well as autosave, however I seem to keep getting errors.

I have a worksheet/tool called "Save Tool", with multiple tabs. What I want is the code to:

1) Unprotect all tabs
2) Perform the tasks of the code, which includes copy/pasting from one tab to the next
3) Re-protect all tabs
4) Save the Tool based on the "Worksheet Name" & "-" & "date in Sheet 1, Cell A3" (ex. Save Tool - 1/15/16)
5) Save the file using the file name created in #4 in a specific destination on the users desktop (C:\Users\John Doe\Desktop\)

The code I have been playing with is:

Sub Macro2()

Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Unprotect "password"

  Columns("B:B").ColumnWidth = 15.71
    Columns("C:C").ColumnWidth = 16
    Sheets("Sheet2").Select
    Columns("B:B").ColumnWidth = 11
    Sheets("Sheet1").Select
    Range("A1").Select
ws.Protect "password"
Next ws

 Dim FName As String

    ActiveSheet.Copy
    With ActiveSheet.UsedRange
        .Copy
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    End With
    Application.CutCopyMode = False


    FName = "C:\Users\John Doe\Desktop\" & Format(Range("A3"), "mmm-d-yyyy") & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=FName, _
                          FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub

Can someone help with the code, or maybe point in the right direction?

Thank you!

 

[dermie_72]

Im a little confused here by your description.
I see the unprotect all tabs but you're protecting it again before you do your copy paste.
Also, if you're copying from one tab to the next you will be replicating data into each of the tabs, instead of having unique ones.
Also...in the for loop you have, you are selecting specific sheets, which means no matter which sheet ws is, you'll end up re-protecting sheet1 only, but unprotecting all the others.
The error on save may come about from there not being a date value in Range A3. Check to see what the value is, another reason may be because the directory is incorrect. Review both and let me know how you go.

 

[chrono2483]

Im a little confused here by your description.
I see the unprotect all tabs but you're protecting it again before you do your copy paste.
Also, if you're copying from one tab to the next you will be replicating data into each of the tabs, instead of having unique ones.
Also...in the for loop you have, you are selecting specific sheets, which means no matter which sheet ws is, you'll end up re-protecting sheet1 only, but unprotecting all the others.
The error on save may come about from there not being a date value in Range A3. Check to see what the value is, another reason may be because the directory is incorrect. Review both and let me know how you go.

Thanks dermie_72 - you're right that the issue was the path being incorrect. When I use my personal path, it works however when I try to create a generic path, that any user could use, it doesn't seem to save in the destination: C:\Users\%username%\Documents\My Received Files\

 

[chrono2483]

I think i may have found the answer, for anyone that may be looking for something similar - this seems to work:

FName = "C:\Users\" & VBA.Environ("Username") & "\Documents\My Received Files\" & Format(Range("G2"), "mmm-d-yyyy") & ".xlsm"
ActiveWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[dermie_72]

perfect. Any time you have a variable, in this case the user name, you would have to put " & variable & " inside the code the consider it. Nice fix.