By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,018 Members | 1,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,018 IT Pros & Developers. It's quick & easy.

Compile Error

P: n/a
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.Paste
End Sub
Sub Delay()
Dim Beg As Long

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

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

Beg = Timer
Do
Loop Until Timer - Beg >= 30
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End Sub
Dec 10 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Saxman" <jo*************@btinternet.com> wrote in message
news:px*****************************@40tude.net...
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.


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.Paste

Call Delay

Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste

Call Delay

' etc...

End Sub

Dec 10 '05 #2

P: n/a
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.CutCopyMode = 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.Paste

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.CutCopyMode = False

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

Call delay() 'wait a bit
Call CopyToSheet(parameter) '"sheet4"

Call delay() 'wait a bit
Call CopyToSheet(parameter) '"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:
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.Paste
End Sub
Sub Delay()
Dim Beg As Long

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

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

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


Dec 10 '05 #3

P: n/a
Steve
He might need a "doevents" in that sort of timer as well
Argusy
Steve Gerrard wrote:
"Saxman" <jo*************@btinternet.com> wrote in message
news:px*****************************@40tude.net...
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.

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.Paste

Call Delay

Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste

Call Delay

' etc...

End Sub


Dec 10 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.