Compile Error

[Saxman]

I need to copy/paste in Excel, but with a delay built in between each
copy/paste operation.

Below is the code which gives me a compile error on the 2nd Sub Delay().
It is a simple copy from sheet 1 to sheets 2,3,4 and 5. Can you see
anything wrong. Help much appreciated.

Thanks

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/12/2005
'
' Keyboard Shortcut: Ctrl+m
'
Range("A1:A5"). Select
Selection.Copy
Sheets("Sheet2" ).Select
ActiveSheet.Pas te
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet3" ).Select
ActiveSheet.Pas te
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet4" ).Select
ActiveSheet.Pas te
End Sub
Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet5" ).Select
ActiveSheet.Pas te
Sheets("Sheet1" ).Select
End Sub

[Steve Gerrard]

Re: Compile Error


"Saxman" <john.h.william s@btinternet.co m> wrote in message
news:px9gfeh9eu ua$.18vr9g2rs4n q4.dlg@40tude.n et...[color=blue]
>I need to copy/paste in Excel, but with a delay built in between each
> copy/paste operation.
>
> Below is the code which gives me a compile error on the 2nd Sub Delay().
> It is a simple copy from sheet 1 to sheets 2,3,4 and 5. Can you see
> anything wrong. Help much appreciated.[/color]

You can't have three different subs all called Delay. Besides, you don't want
to have to run them all separately.

You want to first make *one* sub called Delay, and put only the delay code in
it:

Sub Delay()
Dim Beg As Long

Beg = Timer
Do
Loop Until Timer - Beg >= 30

End Sub

Then make your main sub, which calls Delay multiple times:

Sub Macro1()

Range("A1:A5"). Select
Selection.Copy
Sheets("Sheet2" ).Select
ActiveSheet.Pas te

Call Delay

Sheets("Sheet1" ).Select
Application.Cut CopyMode = False
Selection.Copy
Sheets("Sheet3" ).Select
ActiveSheet.Pas te

Call Delay

' etc...

End Sub

[argusy]

Re: Compile Error

Along with Steve's reply, Can you see what else is wrong?

each "Delay" is repeating the cut and paste until the loop condition is satisfied.
How many times is it doing this cut and paste? Could be one... or two.... or
several. You only need to do this once.

From the structure of your code, it looks reminiscent of the old Basic, BasicA,
MBasic etc. style of writing code. Subroutines do not run in order as you've
shown. Each one has to be triggered (by an 'event') or forced by programming.

Basically you have these subs, and you want them running concurrently

Macro1
delay()
delay() with different parameters
delay() again with different parameters
delay() ditto

VB will not allow you to call four subs by the same name. if you want your code
still separated as subroutines, call them by different names
ie
CopyToSheet2()
CopyToSheet3()
CopyToSheet4()
CopyToSheet5()

Then your program becomes


' Macro1 Macro
' Macro recorded 10/12/2005

' Keyboard Shortcut: Ctrl+m

Range("A1:A5"). Select
Selection.Copy
Application.Cut CopyMode = False

Call delay() 'wait a bit
Call CopyToSheet2()
'
Call delay() 'wait a bit
Call CopyToSheet3()

Call delay() 'wait a bit
Call CopyToSheet4()

Call delay() 'wait a bit
Call CopyToSheet5()

End Sub

private sub CopyToSheet2()
' repeat with other subs with sheets matching sub heading

Sheets("Sheet2" ).Select
ActiveSheet.Pas te

end sub

You could also have only one sub and pass it a parameter

' Macro1 Macro
' Macro recorded 10/12/2005

' Keyboard Shortcut: Ctrl+m

Range("A1:A5"). Select
Selection.Copy
Application.Cut CopyMode = False

Call delay() 'wait a bit
Call CopyToSheet(par ameter) '"sheet2"
'
Call delay() 'wait a bit
Call CopyToSheet(par ameter) '"sheet3"

Call delay() 'wait a bit
Call CopyToSheet(par ameter) '"sheet4"

Call delay() 'wait a bit
Call CopyToSheet(par ameter) '"sheet5"

End Sub

Now that's a different kettle of fish.
BUT - the coding is more efficient, and less to type
(and less to debug)
Also, take a look at VB's intrinsic timer
That can be your research in the VB help for the weekend

Argusy




Saxman wrote:[color=blue]
> I need to copy/paste in Excel, but with a delay built in between each
> copy/paste operation.
>
> Below is the code which gives me a compile error on the 2nd Sub Delay().
> It is a simple copy from sheet 1 to sheets 2,3,4 and 5. Can you see
> anything wrong. Help much appreciated.
>
> Thanks
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 10/12/2005
> '
> ' Keyboard Shortcut: Ctrl+m
> '
> Range("A1:A5"). Select
> Selection.Copy
> Sheets("Sheet2" ).Select
> ActiveSheet.Pas te
> End Sub
> Sub Delay()
> Dim Beg As Long
>
> Beg = Timer
> Do
> Loop Until Timer - Beg >= 30
> Sheets("Sheet1" ).Select
> Application.Cut CopyMode = False
> Selection.Copy
> Sheets("Sheet3" ).Select
> ActiveSheet.Pas te
> End Sub
> Sub Delay()
> Dim Beg As Long
>
> Beg = Timer
> Do
> Loop Until Timer - Beg >= 30
> Sheets("Sheet1" ).Select
> Application.Cut CopyMode = False
> Selection.Copy
> Sheets("Sheet4" ).Select
> ActiveSheet.Pas te
> End Sub
> Sub Delay()
> Dim Beg As Long
>
> Beg = Timer
> Do
> Loop Until Timer - Beg >= 30
> Sheets("Sheet1" ).Select
> Application.Cut CopyMode = False
> Selection.Copy
> Sheets("Sheet5" ).Select
> ActiveSheet.Pas te
> Sheets("Sheet1" ).Select
> End Sub[/color]

[argusy]

Re: Compile Error

Steve
He might need a "doevents" in that sort of timer as well
Argusy


Steve Gerrard wrote:[color=blue]
> "Saxman" <john.h.william s@btinternet.co m> wrote in message
> news:px9gfeh9eu ua$.18vr9g2rs4n q4.dlg@40tude.n et...
>[color=green]
>>I need to copy/paste in Excel, but with a delay built in between each
>>copy/paste operation.
>>
>>Below is the code which gives me a compile error on the 2nd Sub Delay().
>>It is a simple copy from sheet 1 to sheets 2,3,4 and 5. Can you see
>>anything wrong. Help much appreciated.[/color]
>
>
> You can't have three different subs all called Delay. Besides, you don't want
> to have to run them all separately.
>
> You want to first make *one* sub called Delay, and put only the delay code in
> it:
>
> Sub Delay()
> Dim Beg As Long
>
> Beg = Timer
> Do
> Loop Until Timer - Beg >= 30
>
> End Sub
>
> Then make your main sub, which calls Delay multiple times:
>
> Sub Macro1()
>
> Range("A1:A5"). Select
> Selection.Copy
> Sheets("Sheet2" ).Select
> ActiveSheet.Pas te
>
> Call Delay
>
> Sheets("Sheet1" ).Select
> Application.Cut CopyMode = False
> Selection.Copy
> Sheets("Sheet3" ).Select
> ActiveSheet.Pas te
>
> Call Delay
>
> ' etc...
>
> End Sub
>
>
>[/color]