0

I am trying to loop through excel and select rows 1-12 in each worksheet (125 worksheets) in a workbook. But for reasons I dont understand, the code seems to crap out and silently fails after a handful of tabs. 

Sub BB_Rows_Selection()
    Windows("Production_DataBase.xlsx").Activate

    For i = 1 To ThisWorkbook.Sheets.Count
        Sheets(i).Activate
        With ActiveSheet
            Rows("1:12").Select
        End With
   Next i    

   MsgBox "All Rows Selected for BB Update." 
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
LMins
  • 1
  • What do you want to do with these rows? I recommend giving [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a read... – BigBen Jan 15 '19 at 04:45
  • Select is part of the interface between user and Excel. VBA doesn't need it. Worksheets(i).Rows("1:12").Copy makes your intention perfectly clear. However, I doubt that you need to copy 12 x 16348 cells for what you want to do. Perhaps copying a smaller number would be more efficient. Perhaps you like Worksheets(i).Range("A1:W12").Copy – Variatus Jan 15 '19 at 05:27

2 Answers2

0

A Ton of Sheets

  • It is unclear in which workbook you are trying to apply your code.
  • Sheets does include Charts, therefore it may be better to use Worksheets.

The Code

Option Explicit

Sub BB_Rows_Selection()

    Dim i As Integer

    With Workbooks("Production_DataBase.xlsx")
        For i = 1 To .Worksheets.Count
            .Worksheets(i).Activate
            .Worksheets(i).Rows("1:12").Select
        Next
    End With

    MsgBox "All Rows Selected for BB Update."

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
0

Selecting a range uses memory to perform a variety of system tasks and obviously increases with the selections you make. You are facing this issue as your system resources are not enough to handle the memory demand. Probably it would make sense to work on your data in a piece meal manner as suggested earlier. Provided below is an alternative to make it work where the range values are stored in a collection which you can manipulate as required (example showed below)

Sub WorkOnMultipleRange()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim MultiRng As New Collection

    Set wb = Workbooks.Open("Production_DataBase.xlsx")
    For Each ws In wb.Worksheets
           With ws
                Set rng = ws.Rows("1:12")
                MultiRng.Add rng
                DoEvents
            End With
    Next ws
    For Each v In MultiRng
        'do your stuff here
        v.Value = "W"
        DoEvents
    Next v

    MsgBox "All Rows Selected for BB Update."

End Sub
Hansraj
  • 106
  • 4