Loop though and process all open excel workbooks using VBA

The code below does the following:

  1. Loop though all open Excel workbooks in Excel
  2. Sequentially activate each of the workbook
  3. Do some processing related to the active workbook
  4. Save the workbook then move on to the next workbook
Sub Loop_through_all_workbooks()
   Dim wb As Workbook, x As String
   For Each wb In Workbooks
      If wb.Name  ThisWorkbook.Name Then
         x = wb.Name
         Workbooks(x).Activate
      End If 
      Call Do_some_actions() 
      Call ActiveWorkbook.Save
   Next wb 
End Sub 

Sub do_some_actions
   'here goes some code to be executed in the activeworkbook
End Sub

Loop through all sheets in an open excel workbook using VBA

The code below does the following:

  1. Loops through all sheets in the active excel workbooks
  2. activates each of the sheets
  3. calls myFunction subroutine (which would normally be a set of actions done in the active sheet)
Sub myMainProgram ()
   For i = 1 To Sheets.count
      Sheets(i).Activate
      Call myFunction()
   Next i
End Sub

Sub myFunction ()
   'Do something in my activesheet
End Sub

Load a range of non blank excel cells into an array in VBA

The code below will allow you to

  1. Define a range of cells in excel that contain data ( are non-blanks)
  2. Load the data into an array
  3. Loop through the data (… process the records as needed….)
Sub read_nonblank_cells()
   firstrow = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlNext).Row
   firstcolumn = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlNext).Column
   lastrow = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
   lastcolumn = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
   Dim arrData() As Variant 
   arrData = ActiveSheet.Range(Cells(firstrow, firstcolumn), Cells(lastrow, lastcolumn)).Value
   countarray = UBound(arrData)
   For i = 1 To countarray
      ' Do something with your array
      ' each column of data could be addressed with its index
      ' as follows: arrData(i,n)
      ' where n is your column number

Next i End Sub