| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macros
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
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 On Mon, 8 Aug 2011 22:08:00 +1000, "Vacuum Sealed" wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
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 On Mon, 8 Aug 2011 22:08:00 +1000, "Vacuum Sealed" wrote: 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
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 On Tue, 9 Aug 2011 23:36:27 +1000, "Vacuum Sealed" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
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. Well, I gave it a go... Sub Macro126() 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 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 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 18 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 33 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 48 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 63 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 78 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 93 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 108 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 123 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 138 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 1 Application.ScreenUpdating = True End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
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 On Tue, 09 Aug 2011 12:26:01 -0400, GS wrote: 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. Well, I gave it a go... Sub Macro126() 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 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 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 18 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 33 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 48 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 63 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 78 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 93 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 108 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 123 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 138 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 1 Application.ScreenUpdating = True End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
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) On Tue, 09 Aug 2011 20:19:47 -0400, GS wrote: It happens that formulated : Both still work seperatly buy not together 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 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 Please explain what you mean by "how and where". Many thanks for your efforts EPJ On Tue, 09 Aug 2011 12:26:01 -0400, GS wrote: 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. Well, I gave it a go... Sub Macro126() 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 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 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 18 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 33 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 48 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 63 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 78 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 93 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 108 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 123 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 138 Application.Wait _ TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) ActiveWindow.ScrollRow = 1 Application.ScreenUpdating = True End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macros
You can email the file to me here...
gesansomATnetscapeDOTnet -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drop down list using macros in a personal macros workbook | Excel Programming | |||
| Macros in Personal.xls that would create two toolbars and buttonswith assigned macros | Excel Programming | |||
| choose default macros Not Enabled / Macros Enable Setting | Excel Programming | |||
| weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
| Macros inside macros, and pasting into macro code. | Excel Programming | |||
Linear Mode
