VBA repeating steps through multiple sheets

murph131

New Member
Joined
Jun 22, 2015
Messages
2
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

timfritsch

Active Member
Joined
Jun 10, 2004
Messages
343
Try this:

Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,547
Members
430,295
Latest member
amdis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top