Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need advise on mycode
Hi
Would like some advice on how to write a macro to do the actions below: I will create 12 worksheets for each month containing 2 groups of items at the starting of each month. groups - fruits & veg eg Jan worksheet will contains fruits & veg sold in this month. A1 B1 C1 A1 group item name Qty 2 fruits apple 10 3 veg carrot 52 i have a summary page to display the sum for each groups for each month. summary pg A1 B1 1 Jan Feb 2 fruits sum 3 veg sum I have created a macro to create the a new worksheet for each new month. how can i use the code below to work for different month = sub totalgroup() set WS2 as the worksheet for jan set x to be the cell containing fruits sum x.Formula = "=sumif('WS2'!A2:A1000, ""FRUIT"", 'WS2'!F2:F10000)" end sub WS2 will change for different month How do i change it such that it will work for different month?Do i pass in the worksheet name? Thank you for any suggestions provided. kiwis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need advise on mycode
I am not sure that I am getting your problem, but can you not just use
x.Formula = "=sumif('Jan'!A2:A1000, ""FRUIT"", 'Jan'!F2:F10000)" -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... Hi Would like some advice on how to write a macro to do the actions below: I will create 12 worksheets for each month containing 2 groups of items at the starting of each month. groups - fruits & veg eg Jan worksheet will contains fruits & veg sold in this month. A1 B1 C1 A1 group item name Qty 2 fruits apple 10 3 veg carrot 52 i have a summary page to display the sum for each groups for each month. summary pg A1 B1 1 Jan Feb 2 fruits sum 3 veg sum I have created a macro to create the a new worksheet for each new month. how can i use the code below to work for different month = sub totalgroup() set WS2 as the worksheet for jan set x to be the cell containing fruits sum x.Formula = "=sumif('WS2'!A2:A1000, ""FRUIT"", 'WS2'!F2:F10000)" end sub WS2 will change for different month How do i change it such that it will work for different month?Do i pass in the worksheet name? Thank you for any suggestions provided. kiwis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need advise on mycode
Hi
i would like to automate the process, so at the start/end of the month, i would get the raw data & create a new worksheet according to the month with worksheet name as the month then i would like to run the macro to calculate the sum of qty according to group & write back the sum to a summary page. So if i use Jan instead of a variable, then it will be hard coding it & i need to write 12 sumif statements. so i was thinking if i can make it resuable by using a variable maybe you can provide me suggestion on how to tackle this problem. Thank you for your reply. On Apr 17, 4:05 pm, "Bob Phillips" wrote: I am not sure that I am getting your problem, but can you not just use x.Formula = "=sumif('Jan'!A2:A1000, ""FRUIT"", 'Jan'!F2:F10000)" -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... Hi Would like some advice on how to write a macro to do the actions below: I will create 12 worksheets for each month containing 2 groups of items at the starting of each month. groups - fruits & veg eg Jan worksheet will contains fruits & veg sold in this month. A1 B1 C1 A1 group item name Qty 2 fruits apple 10 3 veg carrot 52 i have a summary page to display the sum for each groups for each month. summary pg A1 B1 1 Jan Feb 2 fruits sum 3 veg sum I have created a macro to create the a new worksheet for each new month. how can i use the code below to work for different month = sub totalgroup() set WS2 as the worksheet for jan set x to be the cell containing fruits sum x.Formula = "=sumif('WS2'!A2:A1000, ""FRUIT"", 'WS2'!F2:F10000)" end sub WS2 will change for different month How do i change it such that it will work for different month?Do i pass in the worksheet name? Thank you for any suggestions provided. kiwis- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advise pls | Excel Programming | |||
pls advise | Excel Worksheet Functions | |||
I need some Advise | Excel Programming | |||
Advise VBA | Excel Programming | |||
VBA Advise | Excel Programming |