Hi All,
Ive compiled this code that runs through a folder and edits templates I have created however one of the sheets is protected and I cant seem to figure a way to unprotect each of these sheets as it works through the folderpath and thus leads to an error.
Could anyone help me out on how to fix this ?
EDIT : The worksheet that needs to be unprotected each time is WS1 ("Project Report")
Thanks in advance
Code :
Dim FolderPath As String
Dim FileName As String
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim WS0 As Worksheet
Dim i As Long
Dim j As Long
Dim k As Long
Dim Lastrow As Long
Dim pID As String
Dim pName As String
Application.DisplayAlerts = False
Set WB2 = Workbooks("New tables template.xlsm")
Set WS0 = WB2.Sheets("Control Tab")
Set WS2 = WB2.Worksheets("Tables")
k = 2
FolderPath = "//C..."
FileName = Dir(FolderPath & "*.xlsm")
'----open each workbook
Do While FileName <> ""
Set WB1 = Workbooks.Open(FolderPath & FileName)
Set WS1 = WB1.Worksheets("Project Report")
Set WS3 = WB1.Worksheets("Forecast Summary Forecast")
'---------------routine for each workbook--------------------------
WS2.Activate
WS2.Range("R11:AF34").Select
Selection.Copy
WS1.Activate
WS1.Range("B56:P79").Select
ActiveSheet.Paste
WS1.Rows("250:258").Select
Selection.EntireRow.Hidden = False
Selection.EntireRow.Hidden = False
Selection.RowHeight = 21
WS1.Rows("252:257").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
WS2.Activate
WS2.Range("B4:P15").Select
Selection.Copy
WS1.Activate
WS1.Range("B252:P263").Select
ActiveSheet.Paste
pID = WS1.Cells(6, 5).Value
pName = WS1.Cells(8, 5).Value
WB1.SaveCopyAs FileName:="C/....." & Mid(pID, 1, 6) & "_" & pName & "_" & "Monthly Project Report" & ".xlsm"
FileName = Dir
WB1.Close
Loop
Application.DisplayAlerts = True
End Sub
Bookmarks