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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

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