I'm needing some assistance with this macro.
I have multiple files with multiple sheets, all set up exactly the same. The intent is to have the macro: open the spreadsheet, unlock the sheet, enter the intended information, lock it back up, and move on to the next sheet (however many sheets there may be) and do the same action.
It successfully completes the first cycle, but doesn't go on. Thanks in advance!
Sub Macro2()
Dim FileNameXls, f
Dim wb As Workbook
Dim ws As Worksheet
FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", MultiSelect:=True)
If Not IsArray(FileNameXls) Then Exit Sub
Application.ScreenUpdating = False
For Each f In FileNameXls
Set wb = Workbooks.Open(f)
For Each ws In ActiveWorkbook.Worksheets
ActiveSheet.Unprotect "password"
ActiveWorkbook.Unprotect "password"
Range("BA22").Select
ActiveCell.FormulaR1C1 = "Apple"
Range("BA23").Select
ActiveCell.FormulaR1C1 = "Pear"
Range("BA24").Select
ActiveCell.FormulaR1C1 = "Orange"
Range("BA25").Select
ActiveCell.FormulaR1C1 = "Peach"
Range("BA26").Select
ActiveCell.FormulaR1C1 = "Grape"
Range("BB22").Select
ActiveCell.FormulaR1C1 = "=IF(R[11]C[-40]=RC[-1],1,0)"
Range("BB22").Select
ActiveCell.FormulaR1C1 = "=IF(R[11]C[-40]=RC[-1],1,0)"
Range("BB23").Select
ActiveCell.FormulaR1C1 = "=IF(R[10]C[-40]=RC[-1],1,0)"
Range("BB24").Select
ActiveCell.FormulaR1C1 = "=IF(R[9]C[-40]=RC[-1],1,0)"
Range("BB25").Select
ActiveCell.FormulaR1C1 = "=IF(R[8]C[-40]=RC[-1],1,0)"
Range("BB26").Select
ActiveCell.FormulaR1C1 = "=IF(R[7]C[-40]=RC[-1],1,0)"
Range("BB27").Select
ActiveCell.FormulaR1C1 = "=IF(R[6]C[-40]=RC[-1],1,0)"
Range("BB27").Select
ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next ws
wb.Close SaveChanges:=True
Next f
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I have multiple files with multiple sheets, all set up exactly the same. The intent is to have the macro: open the spreadsheet, unlock the sheet, enter the intended information, lock it back up, and move on to the next sheet (however many sheets there may be) and do the same action.
It successfully completes the first cycle, but doesn't go on. Thanks in advance!
Sub Macro2()
Dim FileNameXls, f
Dim wb As Workbook
Dim ws As Worksheet
FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", MultiSelect:=True)
If Not IsArray(FileNameXls) Then Exit Sub
Application.ScreenUpdating = False
For Each f In FileNameXls
Set wb = Workbooks.Open(f)
For Each ws In ActiveWorkbook.Worksheets
ActiveSheet.Unprotect "password"
ActiveWorkbook.Unprotect "password"
Range("BA22").Select
ActiveCell.FormulaR1C1 = "Apple"
Range("BA23").Select
ActiveCell.FormulaR1C1 = "Pear"
Range("BA24").Select
ActiveCell.FormulaR1C1 = "Orange"
Range("BA25").Select
ActiveCell.FormulaR1C1 = "Peach"
Range("BA26").Select
ActiveCell.FormulaR1C1 = "Grape"
Range("BB22").Select
ActiveCell.FormulaR1C1 = "=IF(R[11]C[-40]=RC[-1],1,0)"
Range("BB22").Select
ActiveCell.FormulaR1C1 = "=IF(R[11]C[-40]=RC[-1],1,0)"
Range("BB23").Select
ActiveCell.FormulaR1C1 = "=IF(R[10]C[-40]=RC[-1],1,0)"
Range("BB24").Select
ActiveCell.FormulaR1C1 = "=IF(R[9]C[-40]=RC[-1],1,0)"
Range("BB25").Select
ActiveCell.FormulaR1C1 = "=IF(R[8]C[-40]=RC[-1],1,0)"
Range("BB26").Select
ActiveCell.FormulaR1C1 = "=IF(R[7]C[-40]=RC[-1],1,0)"
Range("BB27").Select
ActiveCell.FormulaR1C1 = "=IF(R[6]C[-40]=RC[-1],1,0)"
Range("BB27").Select
ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next ws
wb.Close SaveChanges:=True
Next f
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub