Code interrupted error

Please show us the code.

Sid

hello Seamus2626,
the document you sent doesn't show us anything of the error... can you please send us the code, and if possible, with a highlighted area of where the code stops, when error happens ?

THanks,

Thanks Sid
Example.xls

Which code are you referring to?

Sid

That should be good
--Doc1.doc

Run Report Sub

Module2

Thanks
Seamus

Just added the following line and it's working:

Sheets("Output").Select


jppinto

Private Sub Workbook_Open()

Dim r As Long
Sheets("Output").Select
With Sheets("Output")
    r = .Range("H" & Rows.Count).End(xlUp).Row

    .Range("I7").Formula = "=TRIM(MID(INDEX($A:$A,$C7+6),27,8))"

    .Range("I7:I7").Copy .Range("I7:I" & r)
    End With

End Sub

Select allOpen in new window

Oh...it's not this code, it's module 2, right?

Seamus, What is the error that you are getting?

The 1st doc doesn't say anything and the other is corrupt, I believe.

Sid

the error message is

"Code execution has been interrupted"

it errors in Moudule2, Runreport

Thanks
Seamus

Ok few other things.

When you say

Range("A:E").EntireColumn.Hidden = False

Please attache the relevant sheet name before it, else it will be difficult for me or the code to understand what you are referring to. for example

Sheets("Output").Range("A:E").EntireColumn.Hidden = False

Other Changes.

Declare

Dim wb1 As Workbook

at the top of your code and then use it when you open the workbook for example

    Dim wb1 As Workbook
    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")

So that you can refer to the relevant range later

for example

wb1.Sheets(1).Columns("A:A").Copy

Just make the relevant changes in Module 2, RunCalc and Module3.Macro3

and you will be fine :)

Sid

I have done the changes but I don't know which range in which sheet are you referring to. However here is my shot at it. Please amend it to suit your needs :)

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    
    Set wb1 = ActiveWorkbook
    
    wb1.Sheets("Output").Range("A:E").EntireColumn.Hidden = False

    Application.DisplayAlerts = False

    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    '~~> Change path of CP Report.xls
    Set wb3 = Workbooks.Open("G:\CP Report.xls")
    
    wb2.Sheets(1).Columns("A:A").Copy _
    wb3.Sheets(1).Columns("A:A")
    
    wb3.Sheets(1).Columns("A:C").EntireColumn.AutoFit
    wb3.Sheets(1).Columns("A:C").EntireColumn.Hidden = True

    wb2.Close SaveChanges:=False

    wb3.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb3.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Select allOpen in new window

Sid

Hey Sid, thanks for the code, this is the file i refer too

Thanks
Seamus
Example.xls

Ok... Let's wipe off the slate and start all over again.

If you want me to give you an accurate solution, please reply to my questions in the order they are asked.

1) What is the name of the file from where the code is run?
2) What other files names are involved?
3) What are their paths
4) What exactly do you want to do?

Sid

Sorry Sid, i meant thats the source file.

Thats fine, ive adjusted.

However, i got the original error message on line

 Set wb3 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")

Ive attached the changes i made which were amending file path and sheet names.

All in all, im trying to open ML WEEKLY REPORT.xls, copyy its entire column A, and paste it into CP Report.xls, in ColA.

The sheet name for the former is 003409 and the later, output.

Thanks
Seamus

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    
    Set wb1 = ActiveWorkbook
    
    wb1.Sheets("Output").Range("A:E").EntireColumn.Hidden = False

    Application.DisplayAlerts = False

    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    '~~> Change path of CP Report.xls
    Set wb3 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")
    
    wb2.Sheets("003049").Columns("A:A").Copy _
    wb3.Sheets("Output").Columns("A:A")
    
    wb3.Sheets(1).Columns("A:C").EntireColumn.AutoFit
    wb3.Sheets(1).Columns("A:C").EntireColumn.Hidden = True

    wb2.Close SaveChanges:=False

    wb3.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb3.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Select allOpen in new window

That's much better

What is the sheet name of ML WEEKLY REPORT.xls.. "003409" ?
What is the sheet name of CP Report.xls? "output" ?

Also what are their paths?

Sid

Thats it Sid

CP Report.xls, Sheet name is "Output"
ML WEEKLY REPORT.xls, Sheet name is "003409"

Their paths are

("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls") &
("G:\MLHSBC\ML EX Date Report\CP Report.xls")

Thanks
Seamus

Try this and tell me where do you get the error i.e if you get one.

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Application.DisplayAlerts = False

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    Set ws1 = wb1.Sheets("003409")
    
    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")
    Set ws2 = wb1.Sheets("Output")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Select allOpen in new window

Sid

My only question at this point of time regarding the above code pertains to

    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

Do you want to do this in workbook ML WEEKLY REPORT or CP Report?

If it is ML WEEKLY REPORT, then change the above code to

    ws1.Columns("A:C").EntireColumn.AutoFit
    ws1.Columns("A:C").EntireColumn.Hidden = True

Sid

Set ws1 = wb1.Sheets("003409")

Same strange error, "Code Execution has been interrupted"

I have ran this code before and its worked. I dont understand.....

Can you upload the file ML WEEKLY REPORT?

Sid

   ws1.Columns("A:C").EntireColumn.AutoFit
    ws1.Columns("A:C").EntireColumn.Hidden = True

That is for CP Report.xls

Thanks
Seamus

Try this in the meantime

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Application.DisplayAlerts = False

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    
    DoEvents
    
    Set ws1 = wb1.Sheets("003409")
    
    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")
    
    DoEvents
    Set ws2 = wb2.Sheets("Output")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Select allOpen in new window

Sid

Getting stoppped on Do events!

Ok which doevents ? The 1st or the 2nd?

If it is the first then check if you have any code in the workbook open event of ML WEEKLY REPORT.xls or better still, can you upload the file ML WEEKLY REPORT.xls?

Sid

1st Do Event

No code in the Workbook Open for ML WEEKLY REPORT.xls

I uploaded the file earlier

Message 35019691

"example.xls"

That is ML WEEKLY REPORT.xls

Thanks
Seamus

Thanks
Seamus

That is the

Ah!

Change the line

Set ws1 = wb1.Sheets("003409")

to

Set ws1 = wb1.Sheets("003049")

Also remove Doevents.

Now try :)

Sid

Also remove the line

Application.DisplayAlerts = False

Sid

In fact try this

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    Set ws1 = wb1.Sheets("003049")
    
    Set wb2 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\CP Report.xls")
    Set ws2 = wb2.Sheets("Output")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Select allOpen in new window

Sid

This code is gonna crack me up!!

Set ws1 = wb1.Sheets("003049")

Its stopping there.

Then, when i step through, its stops at

wb2.Activate

Then, once complete, again at
 Application.DisplayAlerts = True

What is its problem?!

Thanks
Seamus

I just tried the above code and it works fine for me?

Did you try the above code as it is?

Sid

Also a quick question. From where are you running the code? Which workbook?

Sid

I edited it slightly, no need to re-open CP Report

Thats it.

Same as above, i think!

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    Set ws1 = wb1.Sheets("003049")
    
    Set wb2 = Workbooks("CP Report.xls")
    Set ws2 = wb2.Sheets("Output")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Select allOpen in new window

Im running it from CP Report.xls........

ok try this

Sub RunReport()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set wb2 = ActiveWorkbook
    Set ws2 = wb2.Sheets("Output")

    Set wb1 = Workbooks.Open("G:\MLHSBC\ML EX Date Report\ML WEEKLY REPORT.xls")
    Set ws1 = wb1.Sheets("003049")
    
    ws1.Columns("A:A").Copy ws2.Columns("A:A")
    
    ws2.Columns("A:C").EntireColumn.AutoFit
    ws2.Columns("A:C").EntireColumn.Hidden = True

    wb1.Close SaveChanges:=False

    wb2.Activate

    Call RunCalc
    Call Module3.Macro3
    Call ClearErrors

    With wb2.VBProject.VBComponents("ThisWorkbook").Codemodule
       .DeleteLines 1, .CountOfLines
    End With

    MsgBox "Report Complete,Check Double Div"

    Application.DisplayAlerts = True
End Sub

Select allOpen in new window

Stopped again on  

Set ws1 = wb1.Sheets("003049")

Same error message

I have to shoot off Sid, i will look at this again in the morning with a fresh pair of eyes.

Thanks for your help
Seamus

1) Copy the sheetname from that workbook by double clicking on the sheet tab and pasting it in the code between quotes.
2) Can you post the screenshot of the error message?

Sid

I tried (1) same error

I have attached the error message


error.doc

It is definitely working for me. Can you upload your CP Report.xls file?

Sid

Thanks Sid
CP-Report.xls

Give me 15 mins

Sid

Thanks Sid!

I was able to make it work here. So the only thing that pulls my attention is the path of the file

"G:\MLHSBC\ML EX Date Report\"

Is this on a network location?

Sid

"G:\MLHSBC\ML EX Date Report\"

Thats a shared drive

Do you think i should try the UNC path?

Neither made a difference Sid, i think il have to rethink this report altogether!!

Thanks for all your help on this one

Cheers,
Seamus

Why did you close the thread?

Also did it work when you put it in C:?

Sid

Nope, never worked on C, ive just given up Sid, its 8, i must head home. Tomorrow i may try and think about a different way of doing the report. It seems like the code is doomed!

No it is not :) And I don't want to leave this unfinished ;)

We will wait till tomorrow.

Sid

I appreciate that Sid, i will have a look tomorrow again.

Thanks
Seamus :-)

Found a piece of code to get rid of that annoyhing ba$tard of an error!!

Application.EnableCancelKey = xlDisabled

Thanks for tidying my code up, works now :-)

Glad it is resolved ;)

Sid