Run Macros

  • Thread starter Thread starter jambun
  • Start date Start date
J

jambun

I have 2 Macros, I want to make a macro that will run Macro 126 which
clears all information on current worksheet then goes to worksheet
"Overall Scores", Selects the range B2 to G136, returns to current
worksheet and pastes the copied information. This works fine.
In Macro 128 the scores are then scrolled up the page, this works fine
BUT, I cannot get both macros to work together! Any ideas?
Thanks
EPJ

Sub Macro126()
'
' Macro126 Macro
'

'
Application.ScreenUpdating = False
Range("B2:G136").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B2:G136").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Sheets("Overall Scores").Select
Range("B2:G136").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
Range("A4").Select
End Sub


Sub Macro128()
'
' Macro128 Macro
'

'

Application.DisplayFullScreen = True
ActiveWindow.ScrollRow = 4
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 18
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 33
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 48
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 63
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 78
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 93
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 108
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 123
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 138
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 1

End Sub
 
Hi

I'm going to assume you mean you want the to run concurrently.

Sub RunMyMacros()

Call Macro126
Call Macro128

End Sub

If it something else, please be more specific.

HTH
Mick.
 
Mick, Yes I want to run them concurrently, I have not tried this yet
but will as soon as I can. Every time I tried it, it stoped working
Will ley you know
Thanks
EPJ
 
Mick, I Tried what you said, all that happens is that the timer icon
shows until I press exit, then the first Macro (126) operates
Puzzled
EPJ
 
Hmm..

Ok, Macro126 is probably taking time to run it's course

What may be better is to Call Macro128 at the end of Macro 126 instead,
therefore it will not trigger until 126 is finished

HTH
Mick
 
I'm no Guru, although in your OP you stated that the routine work fine, and
as you have only posted snippets, it is hard to work out what is happening,
for instance, these macro's appear as though they have been done via Macro
Recording and not done manually.

I cannot see any declarations or references as to what type, be it String,
Variable, UDF, or whatever you are defining, plus I don't know why you have
so many CutCopyMode references.

One of the many way-more talented NG contributors may have a better layout
to suggest, in the meantime you could try it this way and see if it
improves.

Sub Macro126()

With Application
.ScreenUpdating = False
End With

Range("B2:G136").Select

With Selection
.ClearContents
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

Sheets("Overall Scores").Select

Range("B2:G136").Select
Selection.Copy
Sheets("Sheet3").Select
Range("A4").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Call Macro128

End Sub


Sub Macro128()
'
' Macro128 Macro
'
Application.DisplayFullScreen = True
ActiveWindow.ScrollRow = 4
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 18
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 33
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 48
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 63
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 78
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 93
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 108
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 123
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 138
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
ActiveWindow.ScrollRow = 1

With Application
.ScreenUpdating = True
End With

End Sub
 
Mike, You are right, I record all my macros via macro recording and
then tweek them to suit my needs. as I do not understand technicaly
how to write macros I spend time working and reworking them until they
work. What I am doing is a score spreadsheet for stroke play at golf.
But when you filter the scores the rows that have no score are
filtered out, and I can not put 1st to last in a column because the
rows with no score is filtered out. Because the winner of stroke has
the lowest score the filtering is done from smallest to largest, and
any score that has not yet been entered is smaller than any score. If
you have a field of say 100 and you have only entered 50 scores to
this stage,when you filter the scores the rows truncate up and any
first to last column truncates with it. So macro 126 copies the
filtered score sheet in ("Overall Scores") and copies them to another
sheet (Sheet 3) which has 1st to last in column A. This part works
fine, Macro128 then alows me to scroll the scores on the overhead
projector so that the players can see where they have come in the
field.Even with your alterations and even with all of your sugestions
the 2 macros will not work together.They both work fine independently
but not together
Thanks
EPJ
 
Vacuum Sealed has brought this to us :
One of the many way-more talented NG contributors may have a better layout to
suggest, in the meantime you could try it this way and see if it improves.
Click to expand...

Well, I gave it a go...
Sub Macro126()
Click to expand...
Application.ScreenUpdating = False
With Range("B2:G136")
.ClearContents
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
Click to expand...
With Sheets("Overall Scores")
.Range("B2:G136").Copy Destination:=Sheets("Sheet3").Range("A4")
Application.CutCopyMode = False
Call Macro128
End Sub


Sub Macro128()
'
' Macro128 Macro
'
Application.DisplayFullScreen = True
ActiveWindow.ScrollRow = 4
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 18
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 33
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 48
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 63
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 78
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 93
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 108
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 123
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 138
Click to expand...
Application.Wait _
TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
ActiveWindow.ScrollRow = 1
Click to expand...
Application.ScreenUpdating = True
 
Both still work seperatly buy not together I needed to change one row
to and also had to put another End With before Call Macro128
.Range("B2:G136").Copy Destination:=Sheets("Sheet3").Range("B2:G136")
I can send this spreadsheet to the group if I am told how and where
Many thanks for your efforts
EPJ
 
It happens that (e-mail address removed) formulated :
Both still work seperatly buy not together
Click to expand...

They work as expected for me (dummy subs) and so I'm not understanding
why you're having a problem.
I needed to change one row
to and also had to put another End With before Call Macro128
Click to expand...

Yeah, sorry about that! I believe I was meaning to eliminate the 'With'
statement...

Sheets("Overall Scores").Range("B2:G136").Copy _
Destination:=Sheets("Sheet3").Range("A4")
.Range("B2:G136").Copy Destination:=Sheets("Sheet3").Range("B2:G136")
I can send this spreadsheet to the group if I am told how and where
Click to expand...

Please explain what you mean by "how and where".
 
First, the range("A4") I only put there so that the copy, paste didn't
leave the pasted cells selected, I have changed this to ("B2") as
sheet 3 is formatted to accept the formatting of the copied cells from
"Overall Scores". It still does not work, and the how and where is
that if I have to send the spreadsheet to you to look at I will need
this information
Thank You
EPJ
Ps I will be away from my computer for the rest of the day(Western
Australian Time)
 
Back
Top