Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying worksheet containing code module
I have a very large workbook containing lots of VBA code.
Among other things, it has to copy one of its own worksheets to a new workbook and save it. The sheet to be copied has an associated coe module. The code is something like this: Sheet.Copy set NewWorkbook = ActiveWorkbook or set NewWorkbook = Workbooks.Add Sheet.Copy Befo=NewWorkbook.Sheets(1) My code then references the newly-copied sheet to make some changes to it. This used to work fine, until recently when I replaced a number of controls on worksheets with their ActiveX equivalents. At some point (I don't know exactly when) the above code ceased working correctly and started giving an "Application or object defined" error. I could find no way to get round it, so eventually I reverted to the version prior to the ActiveX changes. The error persisted. I have tried to circumvent this in various ways, but have had no success. Eventually I tried the workbook on another computer (running Windows 2000 Professional and Office 2000, like the first), and it worked correctly. It seems like something has become corrupted on the first machine and maybe it is related to ActiveX controls. Does anybody have any ideas? I have tried re-installing Office but no luck (I even re- tried reinstalling it after deleting the Office folder in Program Files\Microsoft Office). By the way, if I save this workbook on machine A (where the other problem occurs) the file is much larger than when I save it on machine B. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying worksheet containing code module
Clarifications:
I have corrected a couple of errors in my original post, so please look at the version following. Changes are in upper case. -----Revised Original Message----- I have a very large workbook containing lots of VBA code. Among other things, it has to copy one of its own worksheets to a new workbook and save it. The sheet to be copied has an associated CODE module. The code THAT COPIES THE WORKSHEET is something like this: Sheet.Copy set NewWorkbook = ActiveWorkbook or set NewWorkbook = Workbooks.Add Sheet.Copy Befo=NewWorkbook.Sheets(1) My code then references the newly-copied sheet to make some changes to it. This used to work fine, until recently when I replaced a number of controls on worksheets with their ActiveX equivalents. At some point (I don't know exactly when) the above code ceased working correctly and started giving an "Application or object defined" error WHENEVER I TRY TO REFERENCE THE NEWLY-COPIED SHEET (FOR EXAMPLE, SOMETHING AS SIMPLE AS SELECTING A CELL IN IT). I could find no way to get round it, so eventually I reverted to the version prior to the ActiveX changes. The error persisted. I have tried to circumvent this in various ways, but have had no success. Eventually I tried the workbook on another computer (running Windows 2000 Professional and Office 2000, like the first), and it worked correctly. It seems like something has become corrupted on the first machine and maybe it is related to ActiveX controls. Does anybody have any ideas? I have tried re-installing Office but no luck (I even re- tried reinstalling it after deleting the Office folder in Program Files\Microsoft Office). By the way, if I save this workbook on machine A (where the other problem occurs) the file is much larger than when I save it on machine B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code won't run from a module | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
Where?Worksheet code module or Worksheet_SelectionChange event han | Excel Worksheet Functions | |||
code in module A to not execute a Worksheet_SelectionChange sub of another module | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |