Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Change some sheets in several files

    Hi,

    How can I update some sheets (not all) from several excel files, saved in differente locations (subfolders)?

    In the code below everything it's working but is updating ALL the sheets. How and where can I define the group of sheets needed? For example: from the Sheet "1" to Sheet "31"

    -----------------------
    Option Explicit
    Sub UpdtSomeSheets()

    ' Path - modify as needed but keep trailing backslash
    Const sPath = "D:\Documents"

    Dim sFile As String
    Dim wbkSource As Workbook
    Dim wSource As Worksheet
    Dim ws As Worksheet
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oSubFolder As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    For Each oSubFolder In oFolder.SubFolders
    sFile = Dir(oSubFolder & "\File*.xlsx") 'UPDATE File name or part of it


    Do While Not sFile = ""
    Set wbkSource = Workbooks.Open(Filename:=oSubFolder & "\" & sFile, AddToMRU:=False)

    For Each ws In Worksheets

    'Unprotect sheets
    ws.Unprotect Password:="1"

    'Cells update
    ws.Range("E37") = "New"
    ws.Cells.Replace What:="v1", Replacement:="v1.1", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    'Protect sheets
    ws.Protect Password:="1"

    wbkSource.Close SaveChanges:=True
    Next

    sFile = Dir

    Loop
    Next


    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Sub

    -----------------

    Many thanks for any help



    LL

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Add the line in the DIMs
    Dim i as Integer

    Change the line:
    For Each ws In Worksheets
    to
    For i = 1 to 31
    set ws = worksheets("Sheet "& i)

    Steve

  3. #3
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    Super!

    Many thanks Steve


    LL

Posting Permissions

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