VBA macro sometimes requires running twice to fully complete

[knittelmail]

I have a macro that is a collection of smaller macros that are sometimes themselves a collection of smaller macros. This "big" macro is tied to a button on a worksheet.
The first time I push the button, the macro stops running after the first step. If I keep the worksheet open, manually clean up the mess after the first try, and push the button again, it will complete. It will complete every time until I have to close the worksheet, but won't the next time I open it. My code is ugly, but aside from this one issue, it does what I want it to. Does anyone have any ideas as to why it doesn't work the first time, but does the second? I will try to answer any questions you have. Thank you in advance!

Here is the part where it gets stuck:

Sub ImportandCopy()

Application.Run "PERSONAL.XLSB!GetReport"
---- this is where it gets stuck

Application.Run "PERSONAL.XLSB!UnmergeUnwrap"
Range("A1").Select
Application.Run "PERSONAL.XLSB!Delete_Columns"
Application.Run "PERSONAL.XLSB!CleanStations"
Application.Run "PERSONAL.XLSB!CopyHeader"
Application.Run "PERSONAL.XLSB!CopyStations"
Application.Run "PERSONAL.XLSB!SetBoldValueinCells"
Application.Run "PERSONAL.XLSB!ConvertNums"
Application.Run "PERSONAL.XLSB!FinalCleanUp"

End Sub

The code for "PERSONAL.XLSB!GetReport"
Sub GetReport()

Workbooks.Open Filename:="H:\Production Records\Production Print\Report.xlsx"
Sheets("Report").Copy Before:=Workbooks("Revised Formula Sheet A.xlsm").Sheets(1)
Workbooks("Report").Close SaveChanges:=False

Sheets("Formula Sheet").Activate

' I read somewhere that slowing the process down might help it run better so that is what the counting loop is for.
Dim x As Integer
For i = 1 To 10000
Range("A2").Value = i
Next i

Range("A2").ClearContents

End Sub

 

[Jeffrey Mahoney]

I think you're trying to close the temporary workbook. check if the changes I made below help, if not I'll try something else

Jeff

Sub GetReport()
  Dim RptWB As Workbook

  Set RptWB = Application.Workbooks.Open(Filename:="H:\Production Records\Production Print\Report.xlsx")
  Sheets("Report").Copy Before:=Workbooks("Revised Formula Sheet A.xlsm").Sheets(1)
  RptWB.Close SaveChanges:=False
  
  Sheets("Formula Sheet").Activate
  
  ' I read somewhere that slowing the process down might help it run better so that is what the counting loop is for.
  DoEvents
  'Each process waits for the previous to finish.  If you need to stop the macro you can insert the command
  'above so that it will process mouse clicks and keyboard presses
  
  Range("A2").ClearContents

End Sub

 

[knittelmail]

Thanks for the help Jeff. I tried your changes and got the same result as I was getting before. Your code worked fine, but the process still stalled after the sheet was copied. The report file did not close and the next macro in the list did not run.

I can step through (F8) with no errors and all the macros work even if it is a fresh start.

Could it have something to do with the next macro?

I think you're trying to close the temporary workbook. check if the changes I made below help, if not I'll try something else

Jeff

Sub GetReport()
  Dim RptWB As Workbook

  Set RptWB = Application.Workbooks.Open(Filename:="H:\Production Records\Production Print\Report.xlsx")
  Sheets("Report").Copy Before:=Workbooks("Revised Formula Sheet A.xlsm").Sheets(1)
  RptWB.Close SaveChanges:=False
 
  Sheets("Formula Sheet").Activate
 
  ' I read somewhere that slowing the process down might help it run better so that is what the counting loop is for.
  DoEvents
  'Each process waits for the previous to finish.  If you need to stop the macro you can insert the command
  'above so that it will process mouse clicks and keyboard presses
 
  Range("A2").ClearContents

End Sub

It works today. Yay! Thanks again.

 

[Jeffrey Mahoney]

So, it worked from a fresh start?

 

[knittelmail]

So, it worked from a fresh start?

Today it doesn't.
Yesterday it worked after restarting my computer.
No idea what is going on.

Here is what I have for code:

Sub GetReport()

Dim RptWB As Workbook
Set RptWB = Application.Workbooks.Open(Filename:="H:\Production Records\Production Print\Report.xlsx")
Sheets("Report").Copy Before:=Workbooks("Revised Formula Sheet A.xlsm").Sheets(1)
RptWB.Close SaveChanges:=False
Sheets("Formula Sheet").Activate

End Sub

***This is where it stops the first try. Deleting the newly created tab and pushing the button again it works.

Sub UnmergeUnwrap()

'Workbooks("Revised Formula Sheet A").Activate
Sheets("Report").Activate
ActiveSheet.Cells.Unmerge

Dim ok As Worksheet
For Each ok In Worksheets
ok.Cells.WrapText = False
Next ok

End Sub


Thanks again for whatever you can offer.

James

 

[Jeffrey Mahoney]

So your macros are all on your PERSONAL WB?

is this SUB on the calling WB below?: Sub ImportandCopy()
Workbooks("Revised Formula Sheet A.xlsm")

Any time you reference a sheet without referencing the parent WB, the macro assumes current workbook like here:
Sheets("Report").Activate
It may have been trying to activate that sheet on the PERSONAL WB

I think you should send the WB to the macros in PERSONAL like this: Application.Run "PERSONAL.XLSB!GetReport", ThisWorkbook
I had to reference it like this: Application.Run "PERSONAL.XLSB!Module1.GetReport", ThisWorkbook

Then on the receiving end, the SUB line should look like this: Sub GetReport(TWB). Then you need to reference it in the macro like this: Sheets("Report").Copy Before:=TWB.Sheets(1) and like this: TWB.Activate
Sheets("Formula Sheet").Activate

I know that's a lot to change for a test, but I'm out of Ideas.

Jeff

 

[knittelmail]

So your macros are all on your PERSONAL WB?

is this SUB on the calling WB below?: Sub ImportandCopy()
Workbooks("Revised Formula Sheet A.xlsm")

Any time you reference a sheet without referencing the parent WB, the macro assumes current workbook like here:
Sheets("Report").Activate
It may have been trying to activate that sheet on the PERSONAL WB

I think you should send the WB to the macros in PERSONAL like this: Application.Run "PERSONAL.XLSB!GetReport", ThisWorkbook
I had to reference it like this: Application.Run "PERSONAL.XLSB!Module1.GetReport", ThisWorkbook

Then on the receiving end, the SUB line should look like this: Sub GetReport(TWB). Then you need to reference it in the macro like this: Sheets("Report").Copy Before:=TWB.Sheets(1) and like this: TWB.Activate
Sheets("Formula Sheet").Activate

I know that's a lot to change for a test, but I'm out of Ideas.

Jeff

Sorry, I couldn't quite figure this out. I think I suck at code. What I noticed in trying is that H:\Production Records\Production Print\Report.xlsx won't close.
It opens and gets copied to the WB "Revised Formula Sheet A" just fine as a new WS.

No matter what I tried, I couldn't get the workbook containing the original from H:\Production Records\Production Print\Report.xlsx to close. Nor could I get the code to act on the newly created "Report" worksheet.

At least it works if I delete the newly created "Report" worksheet and push the button again.