title: Executing macro on all worksheets in variant array - issue referring to array objects in for loop
body: <p>I am writing a macro that will loop through 29 worksheets and take data from them to build a formatted estimate in another worksheet.  I have worked out the basic code I'd like to execute on each worksheet, but am having trouble employing it in the for loop.  I suspect that my issue has something to do with the items in the array being string data, or the wrong kind of object.  But I have been unable to resolve it.</p>
  
  <p>UPDATE: I resolved the initial error in the statement below by following the advice in the answers and comments below to remove the unnecessary with block.</p>
  
  <p>I changed this:</p>
  
  <pre><code> With WshtNameCrnt
      If Application.WorksheetFunction.CountA(.Cells) &lt;&gt; 0 Then
          LastRow = .Cells.Find(What:="*", _
                        After:=.Range("A1"), _
                        Lookat:=xlPart, _
                        LookIn:=xlValues, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
      Else
          LastRow = 1
  </code></pre>
  
  <p>To this:</p>
  
  <pre><code>'Find last row on current worksheet      
   If Application.WorksheetFunction.CountA(.Cells) &lt;&gt; 0 Then
              LastRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlValues, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
          Else
              LastRow = 1
          End If
  </code></pre>
  
  <p>So that resolved that problem, because as YowE3K pointed out "Within a With block, the use of a . with no object preceding it tells VBA to apply the method / property to the object defined in the With statement, so (for example) within a With xyz block, the code .Cells is interpreted as xyz.Cells."</p>
  
  <p>The next issue I got in the code was on the line:</p>
  
  <pre><code>'pull sub-categories from current worksheet tab to estimate page
      est_sht.Range(est_sht.Cells(EstLastRow, 3), est_sht.Cells(NumRows + EstLastRow, 3)).Value = Worksheets(WshtNames(WshtNameCrnt)).Range(WshtNameCrnt.Cells(4, 1), WshtNameCrnt.Cells(LastRow, 1)).Value
  </code></pre>
  
  <p>I resolved that by removing the WshtNameCrnt references and changing the statement to:</p>
  
  <pre><code>'pull sub-categories from current worksheet tab to estimate page
          est_sht.Range(est_sht.Cells(EstLastRow, 3), est_sht.Cells(NumRows + EstLastRow, 3)).Value = .Range(.Cells(4, 1), .Cells(LastRow, 1)).Value
  </code></pre>
  
  <p>EDIT: I'm including the entire (not yet polished) macro script for context with some minor changes I've made:</p>
  
  <pre><code>Dim answer As Integer
  Dim InputPercentage As Integer
  Dim ws As Variant
  Dim StartTime As Double
  Dim SecondsElapsed As Double
  
  'declare other variables
  Dim WorkingPercentage As Variant
  Dim EstimateDate As Variant
  Dim LastRow As Variant
  Dim EstLastRow As Variant
  Dim NumRows As Integer
  Dim rng As Range
  Dim SourceRange As Range
  Dim fillrange As Range
  Dim sheetname As String
  
  'declare worksheet variables'
  Dim est_sht As Worksheet
  
  Sub IterateSheets()
  
  'associate worksheet variables with job categories worksheets
  Set est_sht = ActiveWorkbook.Sheets("Estimate Report")
  'declare other variables
  
  Dim WshtNameCrnt As Variant
  Dim WshtNames As Variant
  
  'prompt user whether estimate sheets are completely filled out"
  answer = MsgBox("Have you completed the estimate for all relevant labor categories?", vbYesNo + vbQuestion, "Populate Estimate")
  
  If answer = vbYes Then
  
  'prompt user for markup percentage
  InputPercentage = Application.InputBox("What deposit percentage would you like to charge?", "Enter a number", , , , , , Type:=1)
  
  'prompt user for date to be displayed on estimate
  'EstimateDate = Application.InputBox("What date would you like on the estimate document?  Please enter as MM/DD/YYYY.", "Date")
  
  WorkingPercentage = InputPercentage / 100
  
  'clear out estimate sheet
  est_sht.Cells.Clear
  
  'set row height of top accent bar
  est_sht.Rows("1:1").RowHeight = 10
  
  'set color of top accent bar
  With est_sht.Range("A1:J1").Interior
  .Color = vbBlack
  End With
  
  'set row 2 height
  est_sht.Rows("2:2").RowHeight = 16.5
  
  'set row 3 height
  est_sht.Rows("3:3").RowHeight = 130
  
  'set text formatting
  With est_sht.Rows("3:3").Font
          .Name = "Arial"
          .Size = 20
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .Underline = xlUnderlineStyleNone
          .ThemeColor = xlThemeColorLight1
          .TintAndShade = 0.349986266670736
          .ThemeFont = xlThemeFontMajor
      End With
  est_sht.Rows("3:3").Font.Bold = True
  
  'Date stamp the estimate based on form input
  est_sht.Cells(3, 3).Value = EstimateDate
  
  'title the estimate
  est_sht.Cells(3, 5).Value = "Cost Estimate"
  
  'Insert header row text'
  est_sht.Cells(4, 3).Value = "PROJECT TASKS"
  est_sht.Cells(4, 4).Value = "Cost Estimate"
  est_sht.Cells(4, 5).FormulaR1C1 = InputPercentage &amp; "% Deposit"
  est_sht.Cells(4, 6).Value = "Current Costs"
  
  'format header row of first labor subcategory
  With est_sht.Range("C4:F4").Font
          .Name = "Arial"
          .Size = 9
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .Underline = xlUnderlineStyleNone
          .ThemeColor = xlThemeColorLight1
          .TintAndShade = 0.349986266670736
          .ThemeFont = xlThemeFontMajor
      End With
  est_sht.Range("C4:F4").Font.Bold = True
  
  'create variant array of worksheets
  WshtNames = Array("permits", "project management", "in progress design", "site prep", "services on site", "layout", "concrete", "water management", "framing", "roofing and sheet metal", "electrical", "plumbing", "HVAC", "windows and doors", "exterior finishes", "insulation", "drywall", "painting", "cabinetry", "countertops", "interior finishes", "flooring", "tile", "deck garden", "landscaping", "appliances", "punchlist", "add-ons", "contingency")
  
  'loop through worksheets
    For WshtNameCrnt = LBound(WshtNames) To UBound(WshtNames)
       With Worksheets(WshtNames(WshtNameCrnt))
          'Debug.Print "Cell B3 of worksheet " &amp; .Name &amp; " contains " &amp; .Range("B3").Value
  
          'find last row on estimate page
          With est_sht
              If Application.WorksheetFunction.CountA(.Cells) &lt;&gt; 0 Then
                  EstLastRow = .Cells.Find(What:="*", _
                              After:=.Range("B1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlValues, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
              Else
                  EstLastRow = 1
              End If
          End With
  
          'add sheet name to table
          est_sht.Cells(EstLastRow + 2, 3).Value = .Name
  
          'format sub-header
          est_sht.Range(est_sht.Cells(EstLastRow + 2, 3), est_sht.Cells(EstLastRow + 2, 6)).Font.Bold = True
  
          'Find last row on current worksheet
              If Application.WorksheetFunction.CountA(.Cells) &lt;&gt; 0 Then
                  LastRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlValues, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
              Else
                  LastRow = 1
              End If
          Debug.Print "Last row on " &amp; WshtNameCrnt &amp; " is " &amp; LastRow
  
          'count the number of rows filled with sub-categories'
          NumRows = LastRow - 4
  
        Debug.Print "Number of rows on " &amp; WshtNameCrnt &amp; " is " &amp; NumRows
  
          'pull sub-categories from current worksheet tab to estimate page
          est_sht.Range(est_sht.Cells(EstLastRow, 3), est_sht.Cells(NumRows + EstLastRow, 3)).Value = Worksheets(WshtNames(WshtNameCrnt)).Range(WshtNameCrnt.Cells(4, 1), WshtNameCrnt.Cells(LastRow, 1)).Value
  
          'add sequential numbers next to labor categories on estimate page
          est_sht.Cells(EstLastRow + 2, 2).FormulaR1C1 = "1"
          est_sht.Cells(EstLastRow + 3, 2).FormulaR1C1 = "2"
          Set SourceRange = est_sht.Range(est_sht.Cells(EstLastRow + 2, 2), est_sht.Cells(EstLastRow + 3, 2))
          Set fillrange = est_sht.Range(est_sht.Cells(EstLastRow + 2, 2), est_sht.Cells(EstLastRow + NumRows, 2))
          SourceRange.AutoFill Destination:=fillrange
  
          'set black fill color in sequential numbers sidebar
          With est_sht.Range(est_sht.Cells(EstLastRow + 2, 2), est_sht.Cells(EstLastRow + NumRows, 2)).Interior
          .Color = vbBlack
          End With
          'format text color of sequential numbers
          With est_sht.Range(est_sht.Cells(EstLastRow + 3, 2), est_sht.Cells(EstLastRow + NumRows, 2)).Font
              .ThemeColor = xlThemeColorDark1
              .TintAndShade = 0
          End With
          'format sequential numbers bold
          est_sht.Range(est_sht.Cells(EstLastRow + 3, 2), est_sht.Cells(EstLastRow + NumRows, 2)).Font.Bold = True
  
          'copy labor cost subtotal to estimate page
          est_sht.Cells(EstLastRow + 2, 4).Value = WshtNameCrnt.Range("F2").Value
  
          'populate deposit formula in estimate page
          est_sht.Cells(EstLastRow + 2, 5).FormulaR1C1 = "=RC[-1]*" &amp; WorkingPercentage
          'populate sum formula in "current costs" for labor category
          est_sht.Cells(EstLastRow + 2, 6).FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
  
          'format table around sub-category items and costs
          est_sht.Range(est_sht.Cells(EstLastRow + 2, 3), est_sht.Cells(EstLastRow + NumRows, 6)).Borders(xlDiagonalDown).LineStyle = xlNone
          est_sht.Range(est_sht.Cells(EstLastRow + 2, 3), est_sht.Cells(EstLastRow + NumRows, 6)).Borders(xlDiagonalUp).LineStyle = xlNone
  
          With est_sht.Range(est_sht.Cells(EstLastRow + 2, 3), est_sht.Cells(EstLastRow + NumRows, 6)).Borders(xlEdgeLeft)
              .LineStyle = xlContinuous
              .ColorIndex = 0
              .TintAndShade = 0
              .Weight = xlMedium
          End With
          With est_sht.Range(est_sht.Cells(EstLastRow + 2, 3), est_sht.Cells(EstLastRow + NumRows, 6)).Borders(xlEdgeTop)
              .LineStyle = xlContinuous
              .ColorIndex = 0
              .TintAndShade = 0
              .Weight = xlMedium
          End With
          With est_sht.Range(est_sht.Cells(EstLastRow + 2, 3), est_sht.Cells(EstLastRow + NumRows, 6)).Borders(xlEdgeBottom)
              .LineStyle = xlContinuous
              .ColorIndex = 0
              .TintAndShade = 0
              .Weight = xlMedium
          End With
          With est_sht.Range(est_sht.Cells(EstLastRow + 2, 3), est_sht.Cells(EstLastRow + NumRows, 6)).Borders(xlEdgeRight)
              .LineStyle = xlContinuous
              .ColorIndex = 0
              .TintAndShade = 0
              .Weight = xlMedium
          End With
          With est_sht.Range(est_sht.Cells(EstLastRow + 2, 3), est_sht.Cells(EstLastRow + NumRows, 6)).Borders(xlInsideVertical)
              .LineStyle = xlContinuous
              .ColorIndex = 0
              .TintAndShade = 0
              .Weight = xlMedium
          End With
          est_sht.Range(est_sht.Cells(EstLastRow + 2, 3), est_sht.Cells(EstLastRow + NumRows, 6)).Borders(xlInsideHorizontal).LineStyle = xlNone
  
        End With
    Next WshtNameCrnt
  
  'end of main if/else loop, and end of sub'
  
  Else
      Exit Sub
  End If
  
  End Sub
  </code></pre>
  
  <p>Thanks in advance for any help!</p>