VBA repeating steps through multiple sheets

[murph131]

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

 

[timfritsch]

Try this:

For Each ws In ActiveWorkbook.Worksheets
    With ws
      .Unprotect "password"
      .Range("BA22").FormulaR1C1 = "Apple"
      .Range("BA23").FormulaR1C1 = "Pear"
      .Range("BA24").FormulaR1C1 = "Orange"
      .Range("BA25").FormulaR1C1 = "Peach"
      .Range("BA26").FormulaR1C1 = "Grape"
      .Range("BB22").FormulaR1C1 = "=IF(R[11]C[-40]=RC[-1],1,0)"
      .Range("BB22").FormulaR1C1 = "=IF(R[11]C[-40]=RC[-1],1,0)"
      .Range("BB23").FormulaR1C1 = "=IF(R[10]C[-40]=RC[-1],1,0)"
      .Range("BB24").FormulaR1C1 = "=IF(R[9]C[-40]=RC[-1],1,0)"
      .Range("BB25").FormulaR1C1 = "=IF(R[8]C[-40]=RC[-1],1,0)"
      .Range("BB26").FormulaR1C1 = "=IF(R[7]C[-40]=RC[-1],1,0)"
      .Range("BB27").FormulaR1C1 = "=IF(R[6]C[-40]=RC[-1],1,0)"
      .Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    End With
  Next ws

Tim

 

[murph131]

Perfect solution, I knew I was missing something simple. Thank you!!

 

[timfritsch]

You are welcome. Glad the hear that it worked.


Tim