Looping through Sheets and passing values to an Array

  • Thread starter Thread starter Michael Rhein
  • Start date Start date
M

Michael Rhein

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
 
This macro loops through all the worksheets in a workbook, placing th
value of cell "a1" into an array, then inserts a new page at the star
of the workbook, and inserts the value in successive cells from cel
a2.

Sub loop_and_list()
Dim myarray()
n = 0

For Each ws In ActiveWorkbook.Worksheets
ReDim Preserve myarray(n)
myarray(n) = ws.Range("a1").Value
n = 1 + n
Next

Sheets.Add before:=Sheets(1)
Sheets(1).Select
Range("a2").Select

For n = LBound(myarray) To UBound(myarray)
ActiveCell.Value = myarray(n)
ActiveCell.Offset(1, 0).Select
Next

End Su
 
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
Click to expand...

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
 
Back
Top