Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through Sheets and passing values to an Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through Sheets and passing values to an Array
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 befo=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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through Sheets and passing values to an Array
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
passing values to other sheets | New Users to Excel | |||
Looping between sheets picking up values on the way | Excel Worksheet Functions | |||
Passing array of strings from DLL function to VBA | Excel Programming | |||
PASSING an array to a sub in VBA | Excel Programming | |||
Passing array to a function | Excel Programming |