Menu

Looping through Sheets and passing values to an Array

Search this Thread

#1

Report Post

Posted to microsoft.public.excel.programming

Wouter HM

Wouter HM is offline

external usenet poster

Posts: 99

Default

Looping through Sheets and passing values to an Array

(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

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
passing values to other sheets	[email protected]	New Users to Excel	1	February 23rd 08 02:03 AM
Looping between sheets picking up values on the way	bungie	Excel Worksheet Functions	3	March 5th 06 07:22 AM
Passing array of strings from DLL function to VBA	Jag Man	Excel Programming	0	January 12th 04 10:09 PM
PASSING an array to a sub in VBA	Wombat[_2_]	Excel Programming	2	November 30th 03 11:48 AM
Passing array to a function	GB[_3_]	Excel Programming	3	October 21st 03 09:59 AM

All times are GMT +1. The time now is 01:55 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement