asked on

Excel Macro Help

Hi
I have this macro that was kindly written by someone in this forum. It resets the sheet when cell A5 is changed. I enter a date into cell A5.

Sub Reset_Sheet()
'
' Reset_Sheet Macro
'
'
Range("C40:CU40").Select
Selection.AutoFill Destination:=Range("C7:CU40"), Type:=xlFillDefault
Range("C7:CU40").Select
Range("A5").Select
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.ScrollColumn = 31
Range("A5").Select
End Sub

I also have this simple macro which I activate from the menu bar depending on what day of the week it is this is the one for thursday

Sub ThuEC()
'
' MonEC Macro
' Macro recorded 14/03/2003 by Default
'

'
Range("C3:CU3").Select
Sheets("ErlangC").Select
Range("C10:CU10").Select
Selection.Copy
Sheets("Today").Select
Range("C3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

I would like to incorporate them into one macro that runs when cell A5 is changed on the sheet named today
Ranges for each day of the week is as follows

Monday C4:CU4
Tuesday C6:CU6
Wednesday C8:CU8
Thursday C10:CU10
Friday C12:CU12
Saturday C14:CU14
Sunday C16:CU16

Hope this makes sense
Thanks

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

I generalized your ThuEC and called it from the first sub. Hope that's what you want.

Sub Reset_Sheet()

' Reset_Sheet Macro

Range("C40:CU40").Select
Selection.AutoFill Destination:=Range("C7:CU40"), Type:=xlFillDefault
Range("C7:CU40").Select 'This line doesn't appear to do anything
Range("A5").Select 'This line doesn't appear to do anything
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.ScrollColumn = 31
Range("A5").Select

uniEC 'This statement calls the next sub
End Sub

Sub uniEC()

Dim temp

Select Case WeekDay(Now)
Case vbMonday
Set temp = Range("C4:CU4")
Case vbTuesday
Set temp = Range("C6:CU6")
Case vbWednesday
Set temp = Range("C8:CU8")
Case vbThursday
Set temp = Range("C10:CU10")
Case vbFriday
Set temp = Range("C12:CU12")
Case vbSaturday
Set temp = Range("C14:CU14")
Case vbSunday
Set temp = Range("C16:CU16")
End Select

'
Range("C3:CU3").Select
Sheets("ErlangC").Select
temp.Copy
Sheets("Today").Select
Range("C3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Dave

Deja vu :)

terrafirma

ASKER

Thanks to both of you I used Brettdj because it was there first, thanks again.

No hurry with the other question Brettdj the workbook does the job for the moment but always trying to improve it.

terrafirma

ASKER

Thanks to both of you I used Brettdj because it was there first, thanks again.

No hurry with the other question Brettdj the workbook does the job for the moment but always trying to improve it

terrafirma

ASKER

hi looks like i jumped the gun, i tried yours again brettdj but it only returns wednesdays figures no matter what date is entered

I tried byundt but it only returns blanks in row three

Dave

That is because today is Wednesday.

DayF = Weekday(Now())

This line returns 4, the date for today. If you want to choose the date manually then try

DayF = InputBox(Prompt:="Enter date of 1 (Sunday) to 7 (Saturday)", Default:=Weekday(Now()))

Cheers

Dave

Dave

Doh

DayF = Weekday(Sheets("Today").Range("A5"))

Cheers

Dave

terrafirma

ASKER

I get it, feeling a bit stupid at this point in time. : )

thanks again

Dave

Nope - I misread your intent. The fault was mine.

How is that Melbourne weather? :)

Dave

terrafirma

ASKER

thanks Dave
I tell you melbourne weather is confusing, yesterday it was hot and we had the worst dust storm for 20 years today its raining and i had the heater on in the car coming to work.

Always keeps you guessing