VBA to loop through multiple sheets in a workbook to populate values referencing a list for specific-CodePudding

I have multiple sheets (over 500 ) in a workbook. On each sheet, I need to track data. More than often, 100 sheets will have the same data values. I am looking to utilize a VBA script to loop through a list of sheet names (using an external source like a csv file) and auto populating the fields on the sheets listed in the csv file.

I've done a macro recording of the inputs I need to fill on each sheet:

Sub Fill01()
'
' Fill01 Macro
'
' Keyboard Shortcut: Ctrl d
'
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "MM/DD/YYYY"
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "Engineer_Name"
    Range("I7").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I9").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I11").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I13").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I15").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I17").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I19").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I21").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("A1").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

CodePudding user response：

Type the following macro code into a new module sheet.

  Sub WorksheetLoop()

     Dim WS_Count As Integer
     Dim I As Integer

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For I = 1 To WS_Count

        ' Insert your code here.
        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.
        MsgBox ActiveWorkbook.Worksheets(I).Name

     Next I

  End Sub

CodePudding user response：

Add a worksheet called, say, 'ListOfSheets' to your workbook (with lots of sheets to edit). Type the names of the sheets you want to be updated in a column starting from a1; this is your list (instead of a CSV file you mentioned; a CSV file is also ok but it takes a few more steps). Then switch to the VBA editor, insert a new module and add this code:

Option Explicit

Public Sub DoIt()
   Dim rngeItem As Variant
   For Each rngeItem In ThisWorkbook.Worksheets("ListOfsheets").UsedRange.Cells
       With ThisWorkbook.Worksheets(CStr(rngeItem))
           Debug.Print .Name
           ' Do what you want to do with this worksheet
             .Range("B7") = "MM/DD/YYYY"
             .Range("C7") = "Engineer_Name"
             .Range("I7") = "0"
             .Range("I9") = "0"
             .Range("I11") = "0"
             .Range("I13") = "0"
             .Range("I15") = "0"
             .Range("I17") = "0"
             .Range("I19") = "0"
             .Range("I21") = "0"
             '...
             ' Do whatever else
             '...
       End With
   Next rngeItem
   
End Sub

and run. You don't have to 'select` (cells or ranges) to perform actions/set their values; it is much faster to do it without selecting or activating (as I provided). I don't know why you used the FormulaR1C1 property; you are setting values, not formulae.