Lancer Register Forum banner
1 - 8 of 8 Posts

·
Registered
Joined
·
17 Posts
Discussion Starter · #1 ·
Being the helpful bunch you are, I was wondering if anybody could help with a problem...

I'm writing a macro in excel that needs to calculate some totals from two worksheets (i.e. today and yesterday) however, these sheets have variable names as each day changes the value of today and yesterday.

Yesterday as a variable is called "ydate" and today as a variable is called "tdate", and i populate ydate and tdate with the names of the sheets I need to use at the beginning of each day. I need to use a formula to calculate cell a1 in sheet "ydate" minus cell a1 in sheet "tdate" and the macro always throws up an error with the formula, unless i use the actual sheet name and not the variable.

Any ways around this?
 

·
Super Moderator
Jesus built my car
Joined
·
38,883 Posts
Would help if you post what you have so far, but you can reference other sheets using the Sheets collection

Sheets(yourVarName)

Not 100% sure how you span forumlas across them, but if you post what you have so far I might be able to help more.
 

·
Registered
Joined
·
17 Posts
Discussion Starter · #3 ·
Thanks for your reply.

The formula i want to use in the macro is
Range("A31") = SUM('23May'!B5-'24May'!A30)

However, tomorrow the 23May will become 24May, and 24May will become 25May, so for the sake of my calculations I dim Ydate and TDate as strings (which may be my problem?) and load them up with the sheet names i need to use.

so, i've tried to use =sum('ydate'!B5-'tdate'!A30) but it throws back an error saying it doesn't know what ydate and tdate are, even tho they have been populated with 23May and 24 May respectively.
It could just be that I have the syntax for using variables wrong, but if i do, i don't know what the correct way should be.
 

·
Super Moderator
Jesus built my car
Joined
·
38,883 Posts
amxor said:
Thanks for your reply.

The formula i want to use in the macro is
Range("A31") = SUM('23May'!B5-'24May'!A30)

However, tomorrow the 23May will become 24May, and 24May will become 25May, so for the sake of my calculations I dim Ydate and TDate as strings (which may be my problem?) and load them up with the sheet names i need to use.

so, i've tried to use =sum('ydate'!B5-'tdate'!A30) but it throws back an error saying it doesn't know what ydate and tdate are, even tho they have been populated with 23May and 24 May respectively.
It could just be that I have the syntax for using variables wrong, but if i do, i don't know what the correct way should be.
Try this

Code:
Sheets("24May").Select

yDate = "23May"
tDate = "24May"

Range("A31") = "=Sum('" & yDate & "'!B5-'" & tDate & "'!A30)"
 

·
Registered
Joined
·
17 Posts
Discussion Starter · #5 ·
Thanks, i'll give that a go.
Also, on the last day of each month, whenever that it, it will be necessary to carry forward the figures to a new sheet.
So is there anyway of making that formula to do the following:

Range("A31") = SUM('MayWorkBook31May'!B5-'JuneWorkBook1JUne'!A30)

?

As each month is saved as a different workbook?
 

·
Registered
Joined
·
17 Posts
Discussion Starter · #6 ·
I.e on the 1st working day of each month (which is indicated by the user), i need to open the last day of the previous months workbook and deduct the figures from the first day of this months figures, the formula will look like:

Range("A1")=sum('[2005.04a.xls]29'!$G$4-H4)
but i will substitute a variable in for 2005.04a.xls
as pstats variable contains the string 2005.04a.xls (as it will be populated by an input box

so the formula needs to work like this

Range("A1")=sum('[pstats]29'!$G$4-G4 (from active workbook) )
 

·
Registered
Joined
·
17 Posts
Discussion Starter · #7 ·
That worked perfectly by the way, thanks very much!

I don't need it to open another workbook now, as i've made the macro copy the data i need from the previous month to the new workbook when its created.

Thanks!!
 
1 - 8 of 8 Posts
Top