Worksheet.Copy causes execution to stop

Worksheet.Copy causes execution to stop

• gumtoo

  Sure... I believe this code has all that could have any impact in this case. The code is placed in a seperate module, the constants SN_* is in another module called common, as well with the SheetExists function.

  In the line after 'The next line stops execution..." is where the code stops execution. As mentioned without any errors.

  I have not pasted the UpdateXMLData method. What this method does is to load xml data into a sheet with the name SN_MonthYearACT & "." & SN_Datasheet. This sheet is later on used as datasheet for the data in the report.

  Hope that this might brings somebody a clue on whats going on....

  Best regards
  Anders

  <CODE>

  In MonthYearAct module
  --------------------------------------------------------

  Sub copyDataToMonthYearACT()

  If (SheetExist(SN_MonthYearACT)) Then
  'Then we can do the math
  UpdateXMLData
  If Not SheetExist(SN_MonthYearACT & "." & SN_DataSheet) Then
  GoTo ENDSUB:
  End If
  
  Dim counter As Integer
  counter = 2
  Dim sourceSheet As Worksheet
  Set sourceSheet = Worksheets(SN_MonthYearACT & "." & SN_DataSheet)
  Dim destSheet As Worksheet
  'We want destination to be named with the name "CurMonth YYYY".
  Dim month, year
  month = getMonth
  year = getYear
  Dim sheetName As String
  sheetName = month & " " & year & " ACT"
  If (Not SheetExist(sheetName & "." & SN_Bookkeeping)) Then
  Call CreateBookkeepingSheet(sheetName)
  End If
  If Not SheetExist(sheetName) Then
  On Error GoTo ERROR:
  'The Next line stops execution. No error, no nothing just stops
  Worksheets(SN_MonthYearACT).Copy After:=Worksheets(Worksheets.Count)
  'The code will never reach here
  Set destSheet = ActiveSheet
  destSheet.Visible = xlSheetVisible
  destSheet.name = sheetName
  
  ERROR:
  'The code will never reach here as well...
  ...
  End Sub

  
  '
  ' Creates a sheet with the name Month Year ACT.bookkeeping
  '
  Sub CreateBookkeepingSheet(sheetName As String)
  Dim newSheet As Worksheet
  If Not SheetExist(sheetName & "." & SN_Bookkeeping) Then
  ActiveWorkbook.Worksheets.Add Type:=xlWorksheet
  Set newSheet = ActiveSheet
  newSheet.name = sheetName & "." & SN_Bookkeeping
  newSheet.Visible = xlSheetHidden
  Else
  newSheet = Worksheets(sheetName & "." & SN_Bookkeeping)
  newSheet.Cells.Select
  Selection.ClearContents
  newSheet.Range("A1").Select
  End If
  'insert fields
  newSheet.Range("A1").Value = "1st Active Project"
  newSheet.Range("A2").Value = "1st NewOrder Project"
  newSheet.Range("A3").Value = "1st Completed Project"
  newSheet.Range("A4").Value = "1st Dead Project"
  
  newSheet.Range("B1").Value = 5
  newSheet.Range("B2").FormulaLocal = "=B1+D1+4"
  newSheet.Range("B3").FormulaLocal = "=B2+D2+4"
  newSheet.Range("B4").FormulaLocal = "=B3+D3+4"
  
  newSheet.Range("C1").Value = "Active Count"
  newSheet.Range("C2").Value = "NewOrder Count"
  newSheet.Range("C3").Value = "Completed Count"
  newSheet.Range("C4").Value = "Dead Count"
  
  newSheet.Range("D1").Value = 0
  newSheet.Range("D2").Value = 0
  newSheet.Range("D3").Value = 0
  newSheet.Range("D4").Value = 0
  End Sub

  In Common module
  -----------------------------------------------------
  'Sheet names
  Public Const SN_CompleteOverview = "Complete Overview"
  Public Const SN_MonthYearACT = "Month Year ACT"
  Public Const SN_CustomerTypes = "Customer Types"
  Public Const SN_Bookkeeping = "Bookkeeping"
  Public Const SN_DataSheet = "Datasheet"

  '
  ' SheetExist
  ' Check existance of sheet by name
  '
  Function SheetExist(sheetName)
  SheetExist = False
  For Sheet = 1 To Worksheets.Count
  If Worksheets(Sheet).name = sheetName Then
  SheetExist = True
  GoTo ExitFunc
  End If
  Next Sheet
  ExitFunc:
  End Function

  </CODE>
  

  
  

• Fositron

  Phew.... solved...

  After having tested this on my private pc (with xp sp2, office 2003 pro as well), and found it working perfect, tested it in a vmware image on work (not on domain) and found it working correct, I was able to put some pressure on the IT department who now has found the solution.

  The problem was a single checkbox. Not in XP or Excel, but in the Kaspersky Antivirus Realtime protection. After having upgraded from win2k to XP the company has switched from Norton AV to Kaspersky, therefore the "error" was not on win2k. Apperantly does Kaspersky have a feature to disable several different functions from VBA. Resulting in the above behaviour. Enabling/allowing execution of the mentioned function led all back to normal.

  So finally, i'm able to go for weekend without having to bug my head all weekend with how to circumvent this problem. Nice... :-)

  Best regards and have a nice weekend

  /Anders

  
  

• zurferanders

  Anders -

  Thanks for posting the solution. I would not have guessed it was antivirus software, and I would have suspected Norton before Kaspersky, because of Norton's reputation for not playing nicely with other programs. Just another undocumented issue to keep in mind.

  - Jon
  -------
  Jon Peltier, Microsoft Excel MVP
  Tutorials and Custom Solutions
  http://PeltierTech.com
  _______
  

  
  
  

• Jehan Badshah

  Sorry... my mistake...

  I do have Worksheets.Count, just an error in writing the code on the forum. We have seperate networks for development and internet, so i can't just copy'n'paste... :-(

  /Anders

  
  

• AndersL

  I have sometimes had problems copying sheets, usually after having already copied a number of sheets. As if Excel gets tired of it all and wants to rest. I've resorted to inserting a new sheet and then copying and pasting the intended sheet onto the new one. You need to paste special formulas, formatting, comments, column widths, validation, shapes. Then your code may have to check names in the copied sheet and duplicate them as required. I can't recall what all the gotchas might be, but I know them when I encounter them.

  - Jon
  -------
  Jon Peltier, Microsoft Excel MVP
  Tutorials and Custom Solutions
  http://PeltierTech.com
  _______
  

  
  
  

• kcchesnut

  Hi

  Just a guess, if there is no erro then perhaps the copy worked but left the sheet hidden. Also there is a known problem with worksheet copy which normally generates an error. The fix for this problem is to save the Workbook before copying, may be this is worth a try.

  
  

• John123

  Hi, back from Christmas holiday...

  I still have this problem, I have boiled it down to the following macro code. I have recorded the macro by doing the following:

  Start Macro recording, right click a worksheet tab, select 'move or copy', check 'create a copy' and then stop the recording.

  This results in an additional sheet as expected. It gives the following macro

  <code>

  Sub Macro1()
  '
  ' Macro1 Macro
  ' Macro recorded 03-01-2007 by user
  '

  '
  Sheets("Sheet1").Select
  Sheets("Sheet1").Copy Before:=Sheets(1)
  End Sub

  </code>

  If I step through this code (F8), it will reach the line Sheets("Sheet1").Copy Before:=Sheets(1) and stop execution. Still no errors, and no additional sheet has been added. This is even more visible if the two lines of code are switched so the copy statement runs before the Select. Then the select statement will never be reached.

  I have tried opening the workbook on a win2k machine, and it works fine.

  Does anyone have any solution now

  I have an alternative in the following, Insert new sheet, select all on old sheet to copy, copy that, and paste on new sheet. Will this do exactly the same as the above or will there be possibilities for some references, styles, formatting, etc that are not copied that way

  Best regards

  Anders

  
  

• chaza

  Anytime... I actually don't think that it is an undocumented feature. There is a checkbox for this exact macro command where you can allow/disallow execution of it. But I agree that the way it does stop the execution leaves one wonder... It would have been nice with a popup from kaspersky saying "I stopped execution, because i'm told to" - not just stopping the execution.

  It might be in some logfiles - haven't investigated. But still, as a programmer it's hard to find. But now it's somewhat out in the open.

  /Anders

  
  

• Vincent Fournier

  In the originating code the method have extra lines of code after the one "breaking".

  In fact the extra code make sure that the new worksheet is visible, as well as fill in some additional data.

  I'll try to save the workbook first. And see if that works. If I do a manual copy of the worksheet, it works fine. And just tested the code on a w2k machine, and verified that nothing's wrong here.

  Our company is currently switching all workstations to XP - which means that at Christmas time noone will be able to use the sheet... :-(

  /Anders

  
  

• rwbogosian

  Hey,

  Try this...

  Worksheets(SN_MonthYearAct).Copy After:=Worksheets(Worksheets.Count)

  Cath

  
  

• DiamondDavo

  Can you show us more of your code

  Cath

  
  
• Worksheet.Copy causes execution to stop