James has attended:
Excel VBA Intro Intermediate course
Problems copying between worksheets
Hi, The code below was generated by excel while recording a macro. It also seems to match code provided on the VBA course.
1 Range("A1").Select
2 Range("E5").Copy
3 Sheets("HPD List").Select
4 Range("B3").Select
5 ActiveSheet.Paste
When it runs though it gives an error on line 4:
Run-time error '1004':
Application-defined or Object-defined error
I think it may be something to do with a sheet being selected or acttive but have tried different combinations of selecting or activating the sheet HPD list and cell B3 and nothing works.
Any ideas please.
RE: Problems copying between worksheets
Ok, I've moved the code in to a module and it seems to work properly now.
Given my last 2 questions, is there a general rule about what must be coded within a module as opposed to being able to be coded under forms or sheets?
Thanks
RE: Problems copying between worksheets
Hi James
Sorry for the delay in answering this question.
Code entered under sheets or forms only become active when the sheet/form becomes active.
So as a rule, specially if the code is generic or needs to be used by more than one object it should always be entered in a module.
Code specific to a sheet/form, usually takes the form of event procedures eg controlling what the sheet/form does on Open, Close and all other events and also controlling any buttons and other objects embedded in it.
Regards
Carlos
