Copy Formula Based on Month 2

[EliseFreedman]

I have a workbook containing 2 spreadsheets - my first spreadsheet contains a breakdown of the sales by month for last year along with running totals. It is in the format
January ,Yr To Date(ie. January),February, YrToDate(Jan & Feb)etc
My second spreadsheet contains amongst other things, a column for Last Years Sales to Date.

I would like to be able to insert code which selects the appropriate running total from the first spreadsheet based on the current month and insert it into the second spreadsheet.For Example at the end of this month, I would like to pick up the column which totals January&February.

Does anyone know how to do this

 

[xlbo]

Loadsa ways round this - formula AND code
Look into the MONTH function
VBA:
myMonth = worksheetfunction.month(now())-1
'this will give you the month previous to the one you are in
Select case myMonth
case 1
monthToUse = "January"
case 2
monthToUse = "February"
case 3
etc etc
case else
msgbox "How have you managed to select a month that doesn't exist ??"
end select

Bit more explanation of the front sheet would be useful....do you just need 1 summary number or will you need lots of numbers ???
Will the reference data change (ie are you looking for YTD totals for the same elements each month - nice n easy for formulae)
etc etc Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[VicM]

Elise:

You might try putting the following code in the cell on your second sheet where you want the running totals to appear:

=CHOOSE(Month(Now()),Sheets("name of first sheet&quot.Range("A60&quot,Sheets("name of first sheet&quot.Range("B60&quot, ... ,Sheets("name of first sheet&quot.Range("L60&quot)

Where the A60, B60, ... , L60 are the cell addresses of the running totals on the first sheet.

HTH,

Vic

 

[EliseFreedman]

Thanks xlbo for your help so far. I am on the right track now I think. However, I stiill seem to be getting an error. Last night I set up a dummy spreadsheet and managed to get your code to work with the exception of changing
mymonth=worksheetfunction.month(now()) to
mymonth=month(now())

However, I have come in this morning and tried to use the same code in my real spreadsheet.
I am getting an error message
"Compile Error, wrong number of arguments or invalid property assignment"
I also tried
mymonth=worksheetfunction.month(now())
but am still getting an error message
"bject doesnt support property"

This is my code so far
Dim mydate, mymonth
mydate = Now()
mymonth = Month(mydate)
'this will give you the month previous to the one you are in
Select Case mymonth
Case 1
Range("G7&quot.Select
Case 2
ActiveCell.FormulaR1C1 = "=+ReportSalesbySalesman!RC[-2]"
Range("G7&quot.Select
ActiveCell.FormulaR1C1 = "=+ReportSalesbySalesman!RC[-2]"
Range("G8&quot.Select
ActiveCell.FormulaR1C1 = "=+ReportSalesbySalesman!RC[-2]"
Range("G9&quot.Select

End select

Has anyone any ideas

 

[RobBroekhuis]

month(now) works just fine in XL2000. Have you tried it from the immediate window:

?month(now)

Rob

 

[xlbo]

Elise - that looks fine (technically) - you don't need the selects after the formulae but that's a technicality

The only thing I'd say is that I thought you'd be after the PREVIOS month - in which case you need
myMonth = month(myDate)-1 (which will return 1 as we are in Feb(2)) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[EliseFreedman]

I am still getting the same error message even in the immediate window.

could it be something to do with what is stored in mydate
Now() is giving the value 25/02/03 12:19:21. All I really need and want is 25/02/03

 

[RobBroekhuis]

What version of Excel are you using? If you look up help for the "month" function in VBA, does it tell you it needs more than one parameter? It is normal for now() to contain both date and time - that's not the problem.
Rob

 

[xlbo]

I'm guessing it ain't the Month function that's giving the problem - can't be as it only takes 1 input (a date /variant / string that represents a date)
Also - month(myDate) will work fine with date/time. A date is actually a serial number which is FORMATTED to look like a date
The values to the left of the dp are days and the values to the right are hours / mins / secs. Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[EliseFreedman]

Thanks everyone for your help.

I have managed to work out what was wrong (rob gave me the clue I needed).

I had started creating the spreadsheet using Excel 97. When I recreated it using Excel 2000, it worked successfully.