Jul 28 2022 04:28 AM
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
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
can someone advice where I went wwrong and how can I fix this ??
Thank you
Jul 28 2022 04:58 AM
Use
With wkb
With Sheets("JAN")
.Unprotect
.Range("F3:F5").Value = dt
.Protect
End With
.Close SaveChanges:=True
End With