need advise on mycode

[kiwiiii09]

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

 

[Bob Phillips]

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)

 

[kiwis]

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.