MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy Sheet and Macro Error

  Asked By: Demyan    Date: Aug 26    Category: MS Office    Views: 1981

I have a worksheet with a series of 15 comboboxes. A change event has
been set for each to jump a user to a different location on the sheet
for entry of additonal information (using Range(X).Select).

When I try to make a copy of the sheet, I am getting:
Run time error '1004' - Select method of Range failed

Does anyone have any suggestions for copying a worksheet like this?



4 Answers Found

Answer #1    Answered By: Pearl Garza     Answered On: Aug 26

I find that it sometimes helps to use

dim r as range
set r = activeworksheet.range(x)

Answer #2    Answered By: Finn Jones     Answered On: Aug 26

Sounds as though one or more of the events are firing. You will presumably
get a "debug" option that can tell you which one.

However, easiest would be to answer "no" to "enable macros" when you open
the file. It should copy  OK then.

Answer #3    Answered By: Ramon Davis     Answered On: Aug 26

I am assuming that Excel is compiling the code embedded on the sheet
when it is copied. The error  is on the Range.Select code used to
move around the sheet.

This is an active form, so I need the macros active when it is
opened. The copy  is part of the process in using the form.

Any other thoughts?

Answer #4    Answered By: Sarah Campbell     Answered On: Aug 26

Sorry, didn't gather that from your original question. It sounded as though
you were simply trying to copy  the sheet. I'm now slightly confused ... are
you copying the sheet  by hand, or is your code copying the sheet?

If it is code that is copying the sheet, just disable events before the copy
and turn them back on again after it. That should do the trick.

Didn't find what you were looking for? Find more on Copy Sheet and Macro Error Or get search suggestion and latest updates.