?
Solved

Excel Macro Help

Posted on 2003-03-18
11
Medium Priority
?
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
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 …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

771 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