Adjust cell in multiple workbooks with VBA

New Contributor

Hi everyone

 

I am trying to change cells range in one hundred workbooks using an excel list as a reference to the file location and the data I want to adjust in each workbook.

File Name: File location

Date: the data I want to write in each file at cells range (F3:F5) in each file

 

Inawab770_0-1659006877661.png

 

I am using 2 VBA (those VBA ar from online not main)

 

Sub ReplaceDate(wkbName As String, dt as string)
   
    Dim wkb As Workbook
    Set wkb = Workbooks.Open(Filename:=wkbName)

    With wkb
        .Sheets("JAN").Unprotect.Range("F3:F5").Value = dt
        .Sheets("JAN").Protect
        .Save
        .Close
    End With
End Sub

 

then I use the below to make the loob

 

Sub runForAll()

    Dim rg As Range, sngCell As Range
    Set rg = Range("A2:B101")
    For Each sngCell In rg
    ReplaceDate sngCell.cells(1,1).Value,sngCell.cells(1,2).Value
    Next sngCell
End Sub

but it gave me the blow Error

 

Inawab770_1-1659007113125.png

can someone advice where I went wwrong and how can I fix this ??

 

Thank you

1 Reply

@Inawab770 

Use

    With wkb
        With Sheets("JAN")
            .Unprotect
            .Range("F3:F5").Value = dt
            .Protect
        End With
        .Close SaveChanges:=True
    End With