Advanced VBA Question: Copy data to another sheet to make Validation List
 

I pull sales data reports from a database source on different Accounts,
on different products, for different 1 week time periods, then it takes
much time having to format the data of charting. I would like to use
VBA to help me with the formatting. I will use a UserForm that will
place the following information (A1= # of Accounts, A2= # of Products &
A3= # of Data Weeks), using VBA how do I tell Excel to copy all the
Accounts to a different sheet so I can create a Validata list and also
all the Products. Below is an example of what I'm trying to do:

A1=2
A2=3
A3=3

ACCOUNT A
PRODUCT 1
Base Sales Incremental Sales
1-Jan-06 $51,352 $1,530
8-Jan-06 $65,483 $3,562
15-Jan-06 $70,156 $153

PRODUCT 2
Base Sales Incremental Sales
1-Jan-06 $0 $0
8-Jan-06 $65 $6
15-Jan-06 $4,789
$1,569

PRODUCT 3
Base Sales Incremental Sales
1-Jan-06 $23 $0
8-Jan-06 $8,973 $1,478
15-Jan-06 $101,253 $2,456

ACCOUNT B
PRODUCT 1
Base Sales Incremental Sales
1-Jan-06 $158
$58
8-Jan-06 $158
$36
15-Jan-06 $204
$100

PRODUCT 2
Base Sales Incremental Sales
1-Jan-06 $0
$0
8-Jan-06 $0
$0
15-Jan-06 $0 $0

PRODUCT 3
Base Sales Incremental Sales
1-Jan-06 $99
$2
8-Jan-06 $876
$186
15-Jan-06 $287
$177

The results on another worksheet should look like this:
Col A Col B
ACCOUNT 1 PRODUCT 1
ACCOUNT 2 PRODUCT 2
PRODUCT 3


Much Thanks to anyone who can assist.

Advanced VBA Question: Copy data to another sheet to make Validation List
 

Assume the sheet Data has your data
and you want your lists in sheet Sheet1 in columns A and B

Sub ABC()
Dim sh as Worksheet
set sh = Worksheets("Sheet1")
Dim rng as Range, rng1 as Range
Dim rng2 as Range, sAddr as String

sStr = "Base Sales"
with worksheets("Data")
Set rng = .Cells.Find(What:=sStr, _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
sAddr = rng.Address
do
set rng1 = sh.Cells(rows.count,1).End(xlup)(2)
set rng2 = sh.Cells(rows.count,2).End(xlup)(2)
res = Application.Match(rng.offset(-1,0),sh.Columns(2),0)
if iserror(res) then
rng2 = rng.offset(-1,0)
end if
if rng.offset(-2,0).value < "" then
rng1.Value = rng.offset(-2,0)
end if
set rng = .Cells.findNext(rng)
Loop while rng.Address < sAddr
End if
End With
End Sub

--
Regards,
Tom Ogilvy


wrote in message
ups.com...
I pull sales data reports from a database source on different Accounts,
on different products, for different 1 week time periods, then it takes
much time having to format the data of charting. I would like to use
VBA to help me with the formatting. I will use a UserForm that will
place the following information (A1= # of Accounts, A2= # of Products &
A3= # of Data Weeks), using VBA how do I tell Excel to copy all the
Accounts to a different sheet so I can create a Validata list and also
all the Products. Below is an example of what I'm trying to do:

A1=2
A2=3
A3=3

ACCOUNT A
PRODUCT 1
Base Sales Incremental Sales
1-Jan-06 $51,352 $1,530
8-Jan-06 $65,483 $3,562
15-Jan-06 $70,156 $153

PRODUCT 2
Base Sales Incremental Sales
1-Jan-06 $0 $0
8-Jan-06 $65 $6
15-Jan-06 $4,789
$1,569

PRODUCT 3
Base Sales Incremental Sales
1-Jan-06 $23 $0
8-Jan-06 $8,973 $1,478
15-Jan-06 $101,253 $2,456

ACCOUNT B
PRODUCT 1
Base Sales Incremental Sales
1-Jan-06 $158
$58
8-Jan-06 $158
$36
15-Jan-06 $204
$100

PRODUCT 2
Base Sales Incremental Sales
1-Jan-06 $0
$0
8-Jan-06 $0
$0
15-Jan-06 $0 $0

PRODUCT 3
Base Sales Incremental Sales
1-Jan-06 $99
$2
8-Jan-06 $876
$186
15-Jan-06 $287
$177

The results on another worksheet should look like this:
Col A Col B
ACCOUNT 1 PRODUCT 1
ACCOUNT 2 PRODUCT 2
PRODUCT 3


Much Thanks to anyone who can assist.