Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation - Temporarily Make dropdown list wider | Excel Discussion (Misc queries) | |||
How do I make a Validation List in Excel, using data on a second | Excel Discussion (Misc queries) | |||
HOW DO I MAKE VALIDATION LIST CONTAING DATA FROM A DIFFERENT BO | Excel Discussion (Misc queries) | |||
Make Data validation List Alphabetical?? | Excel Worksheet Functions | |||
Sheet change event and list validation question | Excel Programming |