ExcelBanter - View Single Post - Looping through Sheets and passing values to an Array

Wouter HM

(Michael Rhein) wrote in message om...
Guys,
I have a Salesman information workbook that have Salesman Name in
every Sheets. This workbook change dynamically every months. And
Salesman Name begin in Range B7 and Total Sales at the end of Column I
(I know this can be done with ActiveCell.Offset(0,9).Select and
ActiveCell.Row.End(xlDown).Select).

but I need a code that can loop through sheets, passing values to
SalesmanName and SalesTtl Array, Insert a new sheet at the begining,
list values in both Arrays as a Summary by Salesman.

TIA
mr

Hi Michael,

I Suggest you keep the summary sheet constant in your workbook.
Change it name to "Summary"

In column A you place the sheet names
EG: A1: “Sheet1”
A2: “Sheet2”

Next use this macro:
Public Sub FindSales()
Dim strName As String
'
Sheets("Summary").Select
Range("A1").Select
Do
strName = ActiveCell.Value
ActiveCell.Offset(0, 1).Value = _
Sheets(strName).Range("B7").Value
ActiveCell.Offset(0, 2).Value = _
Sheets(strName).Range("I1").End(xlDown).Value
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
End Sub

If you do not want to see the column with the sheet names you can hide it.

Good Luck.

Wouter HM