Results 1 to 2 of 2

VBA to unprotect worksheet as it works through folderpath

Threaded View

  1. #1
    Registered User
    Join Date
    02-28-2018
    Location
    Edinburgh
    MS-Off Ver
    2016
    Posts
    12

    VBA to unprotect worksheet as it works through folderpath

    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
    Last edited by Bisky; 08-27-2018 at 08:22 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro Unprotect works 99 out of 100 times
    By NewYears1978 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-26-2017, 11:30 AM
  2. Macro Unprotect works 99 out of 100 times
    By NewYears1978 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-19-2017, 05:38 PM
  3. Macro copy data from folderpath to new workbook without open the folderpath
    By nspeng87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-12-2017, 02:43 AM
  4. [SOLVED] Specify FolderPath from user-supplied value in cell
    By Jonny_XL in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-13-2016, 12:50 AM
  5. Unprotect & Protect Multiple works sheets with a Macro
    By robertguy in forum Excel General
    Replies: 3
    Last Post: 03-21-2013, 02:46 PM
  6. Macro to unprotect then reprotect sheet only works once
    By Puffy1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-30-2012, 12:48 AM
  7. [SOLVED] Why 'Unprotect' works for 2003 but not 2000?!
    By OrientalPearl in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 04-02-2006, 08:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1