I have found an interesting situation. I have a large excel file. It was over 20 sheets, one sheet for each region. I have a macro that loops through the sheets, moves each sheet to a new workbook, puts a password on it, saves it based on the region name, closes the new file.
Then I email each sheet to the region manager, they input data into the unprotected cells, send it back to me, etc, etc.
I have one region that has continually been unprotecting my file, so I decided to call them and ask how they are getting around the protection. Their answer was simple, they said we are using the password "simpson", which happens to be the name of their region. BUT, this is not the password my code establishes, not even close. I opened the file and sure enough the password "simpson" does work, AND the password the VBA sets also works ("Saving Grace"). I tried some of the other workbooks that are created and "simpson" seems to work on them all.
Does anybody have any idea how two passwords could be working? This has me stumped. Any thoughts would be appreciated. Thank you!
Then I email each sheet to the region manager, they input data into the unprotected cells, send it back to me, etc, etc.
I have one region that has continually been unprotecting my file, so I decided to call them and ask how they are getting around the protection. Their answer was simple, they said we are using the password "simpson", which happens to be the name of their region. BUT, this is not the password my code establishes, not even close. I opened the file and sure enough the password "simpson" does work, AND the password the VBA sets also works ("Saving Grace"). I tried some of the other workbooks that are created and "simpson" seems to work on them all.
Does anybody have any idea how two passwords could be working? This has me stumped. Any thoughts would be appreciated. Thank you!
Code:
Sub SeparateSheets()
Dim wb As Workbook
Dim ws As Worksheet
Dim wbNew As Workbook
Dim wsNew As Worksheet
Dim CurrMonthCol As String
Dim CurrMonthNum As String
Dim CurrMonthYr As String
Dim VP As String
Dim DeleteMonth As String
CurrMonthCol = Sheets("notes").Range("B4").Value
CurrMonthNum = Sheets("notes").Range("B6").Value
CurrMonthYr = Sheets("notes").Range("B2").Value
VP = Sheets("notes").Range("A1").Value
DeleteMonth = Sheets("notes").Range("B9").Value
Set wb = ActiveWorkbook
'Create Directory for Outbound
If Dir("J:\Sales Technology\BSmith\Projections\Outbound\" & CurrMonthYr & " " & CurrMonthNum, vbDirectory) = "" Then
MkDir "J:\Sales Technology\BSmith\Projections\Outbound\" & CurrMonthYr & " " & CurrMonthNum
Else
MsgBox (CurrMonthYr & " " & CurrMonthNum & " already exists in the Outbound folder.")
End If
'Create Directory for Inbound
If Dir("J:\Sales Technology\BSmith\Projections\Inbound\" & CurrMonthYr & " " & CurrMonthNum, vbDirectory) = "" Then
MkDir "J:\Sales Technology\BSmith\Projections\Inbound\" & CurrMonthYr & " " & CurrMonthNum
Else
MsgBox (CurrMonthYr & " " & CurrMonthNum & " already exists in the Inbound folder.")
End If
'Separate Sheets
For Each ws In wb.Worksheets
Select Case ws.Name
Case "Projection System Load", "Budget System Load", "Consolidated", "HOME", "notes", "LY Actuals", "LY Actuals Raw", "SimpleSummaryDetailDump"
Case Else
'Blank out cells in Curr Month, based on formula within cell. Only blanks it out if the value = Bgt$. Otherwise it leaves it cuz that means they have altered it.
ws.Copy
Set wbNew = ActiveWorkbook
Set wsNew = ActiveSheet
If DeleteMonth = "Y" Then
Application.ReferenceStyle = xlR1C1
Range(CurrMonthCol & ":" & CurrMonthCol).Replace What:="=RC[-1]", Replacement:=""
Application.ReferenceStyle = xlA1
End If
wsNew.Protect "SavingGrace"
wbNew.Protect "SavingGrace"
wbNew.SaveAs "J:\Sales Technology\SEisman\Projections\Outbound\" & CurrMonthYr & " " & CurrMonthNum & "\Projection For " & ws.Name & ".xlsx"
wbNew.Close
End Select
Next ws
MsgBox "Worksheets Separated!", vbOKOnly
End Sub
