vba automation fault while copying sheet

tnecniv

New Member
Joined
Aug 14, 2011
Messages
2
Hi!
I have a somewhat frustrating problem with my Excel 2007 vba code.
I think it might be a weird memory issue.
Here is what I do and what happens:

I have a procedure that copys a template worksheet within the workbook using "Copy After", then renames the copy and fills in some data.
Now there is a button on that copy which triggers a procedure that copys the activeSheet (the copy of the template, you remember? :)) into a new workbook saves the new file and immediately closes the new workbook.

Code:
PHP:
Sub SaveAsWorkbook()

    Application.ScreenUpdating = False

    Path = ActiveWorkbook.Path
    ActiveSheet.Copy
    Range("AK2").Clear
    ActiveSheet.Buttons(1).Delete
    ActiveWorkbook.SaveAs Filename:=Path & "\Doku_" & Replace(Range("D6").Value, ", ", "_") & "_" & Replace(Range("D8").Value, " ", "_") & ".xlsm", FileFormat:=52
    ActiveWorkbook.Close
    Application.CutCopyMode = False
 
    Application.ScreenUpdating = True
End Sub
Everything works perfectly fine so far.
But if I open the newly created file with the one sheet I copied while my "main" workbook is still open, I cant execute the procedure which is supposed to copy the template sheet anymore.
It's really weird and not so easy to explain, but even if I close all excel instances and re-open both files and just click the "copy template procedure" it errors out with an "automation error". The exact line is
PHP:
ActiveWorkbook.Worksheets("Sheet_Template").Copy After:=ActiveWorkbook.Worksheets("Sheet_Template")
Even when I open my seperate workbook and close it again and try the procedure after that the error occures.
I hope I made myself understood and provided enough information. Feel free to ask for more code or whatever question there might be :)
Thanks a lot in advance
So long Vincent
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I'm assuming you only want to run the "copy" functions from the workbook that has the template sheet in it, yes? So, using ThisWorkbook instead of ActiveWorkbook might resolve this, and other, problems. This won't matter WHAT the activeworkbook is when you run the macro, it will apply the copy command to the template sheet in the correct workbook.
 
Upvote 0
Thanks for the reply but unfortunatly that didn't solve the problem. I also checked which workbook is active when i'm trying to copy the template and it's the correct one. So that doesn't seem to be the problem... It's really weird since excel is completly crashing when the error occurs I can't even properly debug it, I have to kill the process :eeek:
Any suggestions?
Thanks, Vincent
 
Upvote 0
I just noticed you tried to create a variable called "Path", you can't do that. PATH is a VBA function already. ALWAYS use unique variable names, always declare them at the top of your macros, and even consider naming conventions that remind you what kind of variable it is you're using, very useful in longer more complex macros.

In VBEditor, go to Tools > Options and turn on AUTO SYNTAX CHECK, too, so you get the OPTION EXPLICIT added automatically to the top of all macros you create, this turns on code-checking and will save you countless hours of chasing down errors.

Rich (BB code):
Option Explicit

Sub SaveAsWorkbook()
Dim strPATH As String

Application.ScreenUpdating = False

    strPATH = ActiveWorkbook.Path
    ActiveSheet.Copy
    Range("AK2").Clear
    ActiveSheet.Buttons(1).Delete
    ActiveWorkbook.SaveAs Filename:=strPATH & "\Doku_" & Replace(Range("D6").Value, ", ", "_") & "_" & Replace(Range("D8").Value, " ", "_") & ".xlsm", FileFormat:=52
    ActiveWorkbook.Close
    Application.CutCopyMode = False
 
Application.ScreenUpdating = True
End Sub


Fingers crossed that these corrections to your programming choices also resolve this issue...
 
Upvote 0

Forum statistics

Threads
1,224,517
Messages
6,179,233
Members
452,898
Latest member
Capolavoro009

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top