Macro running smoothly through button, but jumps to "break mode" through another macro or F8

[casio1970]

Hi all,

I have a weird problem that I stuck with. I have the attached file which copies 4 sheets to another workbook and saves it.

Macro runs smoothly but I'am using this shortened code inside another file and this macro is called inside another macro in that file which causes the problem. When trying to debug step-by-step with F8, I get the weird problem and the code is interrupted, I get the message "Can't execute in break mode error" .

Your help will be highly appreciated.

Sub Byebye2()
'

Dim Aname As String
Dim GetBook As String
Dim YeniBook As String
Dim IslProg As String


On Error Resume Next

IslProg = Application.ActiveWorkbook.Path


GetBook = ActiveWorkbook.Name

    Sheets(Array("Cikti1", "Cikti2", "Cikti3", "Cikti4")).Select
    Sheets(Array("Cikti1", "Cikti2", "Cikti3", "Cikti4")).Copy
    
  
    Sheets("Cikti1").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
With ActiveWindow
      .ScrollRow = 1
      .ScrollColumn = 1
      .ActivePane.VisibleRange.Cells(1).Select
End With

    
   
    Sheets("Cikti2").Select
    Cells.Select
    Selection.Copy
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
With ActiveWindow
      .ScrollRow = 1
      .ScrollColumn = 1
      .ActivePane.VisibleRange.Cells(1).Select
End With
    
    Sheets("Cikti3").Select
    Cells.Select
    Selection.Copy
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
With ActiveWindow
      .ScrollRow = 1
      .ScrollColumn = 1
      .ActivePane.VisibleRange.Cells(1).Select
End With
    
    Sheets("Cikti4").Select
    Cells.Select
    Selection.Copy
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
With ActiveWindow
      .ScrollRow = 1
      .ScrollColumn = 1
      .ActivePane.VisibleRange.Cells(1).Select
End With


    
YeniBook = ActiveWorkbook.Name
   

Workbooks(GetBook).Activate


Worksheets("Cikti1").Select

Aname = ActiveWorkbook.Sheets("Cikti1").Range("f33").Value

Workbooks(YeniBook).Activate

Worksheets("Cikti1").Name = "OzetCikti1"
Worksheets("Cikti2").Name = "OzetCikti2"
Worksheets("Cikti3").Name = "OzetCikti3"
Worksheets("Cikti4").Name = "OzetCikti4"


Worksheets("OzetCikti1").Select


YeniBook = ActiveWorkbook.Name

Application.DisplayAlerts = False
   

ActiveWorkbook.SaveAs Filename:=IslProg & "\SON_" & Aname, FileFormat:=1, CreateBackup:=False

   

Application.DisplayAlerts = True

Application.EnableCancelKey = XlEnableCancelKey.xlInterrupt

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

  
End Sub

 

[Siddharth Rout]

I have the attached file which copies 4 sheets to another workbook and saves it.

I could not see the file.

Couple of tips.

1. Avoid the use of On Error Resume Next unless it is absolutely necessary. It is like telling the application to "Shut up". In such a case, one may not know what is the exact problem as the error messages will get supressed.
2. Avoid the use of .Select and .Activate. Three main reasons why .Select, .Activate, Selection, Activecell, Activesheet, Activeworkbook, etc. should be avoided
   1. It slows down your code.
   2. It is usually the main cause of runtime errors.
   3. The "active" cell, sheet, workbook may not be the one you think is active. Create proper objects and work with them.
3. Indent and comment your code. Easier to read and maintain.
4. Give meaningful names to your variable.

Your code can be written as (UNTESTED)

Option Explicit

Sub Sample()
    Dim wbThis As Workbook
    Dim wbNew As Workbook
    Dim NewPath As String
    Dim NewFileName As String
    
    '~~> If you are running code from the same workbook then use ThisWorkbook
    Set wbThis = ThisWorkbook
    
    With wbThis
        NewPath = .Path
        NewFileName = .Sheets("Cikti1").Range("f33").Value
    
        .Sheets(Array("Cikti1", "Cikti2", "Cikti3", "Cikti4")).Copy
    End With
    
    '~~> This is the new workbook with the copied sheets
    Set wbNew = Workbooks(Workbooks.Count)
    
    '~~> Instead of copy paste special in every sheet
    Dim ws As Worksheet
    
    With wbNew
        '~~> Loop through all sheets and convert formula to values
        For Each ws In .Worksheets
            ws.UsedRange.Value = ws.UsedRange.Value
        Next ws
    
        .Worksheets("Cikti1").Name = "OzetCikti1"
        .Worksheets("Cikti2").Name = "OzetCikti2"
        .Worksheets("Cikti3").Name = "OzetCikti3"
        .Worksheets("Cikti4").Name = "OzetCikti4"
    End With

    Application.DisplayAlerts = False
    wbNew.SaveAs Filename:=NewPath & "\SON_" & NewFileName, FileFormat:=1, CreateBackup:=False
    Application.DisplayAlerts = True
    Application.EnableCancelKey = XlEnableCancelKey.xlInterrupt
    Application.Calculation = xlAutomatic
End Sub

Test the above and see if you are still getting an error?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Macro running smoothly through button, but jumps to "break mode" through another macro or F8
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[casio1970]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Macro running smoothly through button, but jumps to "break mode" through another macro or F8
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sorry Fluff,
I was really not aware of this rule, will be more careful afterwards..

 

[casio1970]

Siddharth,

Thank you very much. Your neat code is really appreciated. I have tested your code. My code was also working, your code is more neat, but the problem is same for both, ie both macro runs without problem if you run them from button or developer tab. But when I try to debug the code by F8, both codes break out at the same point, at sheets.copy...

Do you have any idea, is my Excel corrupted some how?

 

[Siddharth Rout]

Siddharth,

Thank you very much. Your neat code is really appreciated. I have tested your code. My code was also working, your code is more neat, but the problem is same for both, ie both macro runs without problem if you run them from button or developer tab. But when I try to debug the code by F8, both codes break out at the same point, at sheets.copy...

Do you have any idea, is my Excel corrupted some how?

View attachment 54681

That is indeed strange. I can execute the code in break mode. See this

 

[Fluff]

But when I try to debug the code by F8, both codes break out at the same point,

If you click Ok, then you should be able to carry on running the code.

 

[casio1970]

If you click Ok, then you should be able to carry on running the code.

Dear Fluff,

It is exactly as you said... But the main problem : I'm using this macro inside another macro in the master file. So, If I run this macro standalone : No Problem, but when the master macro calls this macro I face the break out..

I'm still trying to figure the reason, your helps will be appreciated...

Thanks again

 

[Siddharth Rout]

but when the master macro calls this macro I face the break out.....

Can you a share a little more about the master macro?