?
Solved

Excel Macro Help

Posted on 2003-03-18
11
Medium Priority
?
329 Views
Last Modified: 2007-12-19
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


0
Comment
Question by:terrafirma
  • 5
  • 5
11 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 600 total points
ID: 8163777
Hi terra,

I haven't forgotten your outstanding question. With this one try


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
Dayofweek
End Sub


Sub Dayofweek()
    Range("C3:CU3").Select
    Sheets("ErlangC").Select
    DayF = Weekday(Now())
    Select Case DayF                                 ' returns day pof the week as num 1 - 7 (Sun - Sat)
        Case 1
            Range("C16:CU16").Select
        Case 2
            Range("C4:CU14").Select
        Case 3
            Range("C6:CU6").Select
        Case 4
            Range("C8:CU8").Select
        Case 5
            Range("C10:CU10").Select
        Case 6
            Range("C12:CU12").Select
        Case Else
            Range("C14:CU14").Select

    End Select
    Selection.Copy
    Sheets("Today").Select
    Range("C3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Cheers

Dave
0
 
LVL 81

Expert Comment

by:byundt
ID: 8163870
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
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 8163929
Deja vu :)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:terrafirma
ID: 8164088
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.
0
 

Author Comment

by:terrafirma
ID: 8164089
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
0
 

Author Comment

by:terrafirma
ID: 8164179
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

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 8164218
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
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 8164229
Doh

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

Cheers

Dave
0
 

Author Comment

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

thanks again
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 8165793
Nope - I misread your intent. The fault was mine.

How is that Melbourne weather? :)

Dave
0
 

Author Comment

by:terrafirma
ID: 8169785
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question