MACRO to Protect Worksheet and Autosave File

chrono2483

Board Regular
Joined
Aug 23, 2014
Messages
160
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:

Code:
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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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

Board Regular
Joined
Aug 23, 2014
Messages
160
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

Board Regular
Joined
Aug 23, 2014
Messages
160
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

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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. :)
 

Forum statistics

Threads
1,141,309
Messages
5,705,674
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top