Copy Worksheets

[AC12]

Seems this should be easy, but I need some help. Two VBA statements. First sends execution to a function in the module, though it should only clear a range. IF I comment that out, second statement sends execution to a different function in the module. What can I be doing wrong?

Sub CopyMySheet()
'Worksheets("TempServe").Range("A1:A500").ClearContents
Worksheets("ProvidedServices").Range("A1:A500").Copy Worksheets("TempServe").Range("A1:A500")

End Sub


Thanks for your help

 

[Norie]

What do you mean by 'sends execution'?

Do you mean a function is being called when you run either line of code?

If that is happening it means that the code is triggering calculation, and if you don't want that to happen set calculation to manual with this at the start of the code,

Application.Calculation = xlCalculationManual

and set it back to automatic with this at the end of the code.

Application.Calculation = xlCalculationAutomatic

 

[My Aswer Is This]

So what is this not doing for you?
The script looks Ok to me.

 

[AC12]

When I step through the code it gets to the clear contents statement and does not clear the contents, however the debugger shows that control has now passed to a function in the module. If I can get to the copy statement and I step through it, it does not put the data in the second sheet. however, the control then passes to a different function within the module.

 

[My Aswer Is This]

edit

 

[AC12]

I've sent you all the code in that sub procedure. When I "step through the code" when it gets to the copy statement the procedure does not copy anything. Again, using step through on the copy statement, it doesn't go to the end sub statement, instead it moves to Function ClientNameFromID(MyID As Integer), which is a function in the same module. I hope this clarifies it.

 

[My Aswer Is This]

Looking at your code again here is what happening

'Worksheets("TempServe").Range("A1:A500").ClearContents
The above line of code clears the Range "A1:A500" in sheet "TempServe"




Worksheets("ProvidedServices").Range("A1:A500").Copy Worksheets("TempServe").Range("A1:A500")

In the above line of code Copies the range "A1:A500" in sheets("ProvidedServices") and paste the values into Range ("A1:A500") of sheet "TempServe"

So your clearing the contents and then repasting other data back into the same range you just cleared.

 

[Trebor76]

What text colour is 'Worksheets("TempServe").Range("A1:A500").ClearContents in? The single quote at the start of the line says to me it's simply text (would be green) which code ignores.

 

[AC12]

I've simplified the code to:

Sub CopyMySheet()
Worksheets("ProvidedServices").Range("A1:A500").Copy Worksheets("TempServe").Range("A1:A500")
End Sub


Using step into
Click one highlights Sub CopyMySheet()
Click two highlights Worksheets("ProvidedServices....
Click three highlights Function ClientNameFromID(MyID As Integer)

That is, it skips the End Sub statement. It does make the copy, but I'm don't understand why the Function statement is now highlighted.

 

[My Aswer Is This]

Putting a ' before a line of code only tells the script to ignore that line of code it does not tell the script to go to next function in the same module.