0

Having an issue on my VBA where I keep getting error 9 at the same spot, the last record below. I found out if i remove Application.ScreenUpdating = False it eliminates the issue. but I want that on because of the shear time it takes the VBA to run with it being True. Below is my code hope someone may have some insight. the workbook is opened already so dont know why it has issues to call on that tab.

Sub PreSpecialRun()
Dim i#
Dim fromPath As String
Dim Special$
Dim wkb As Workbook, wkbFrom As Workbook

Application.ScreenUpdating = False

'   Check if WKB is open if open (close). if not continue
If WorkbookIsOpen(fromPath & "ALL.xls") Then
Workbooks(fromPath & "ALL.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close False
End If

'   Check if WKB is open if open (close). if not continue
If WorkbookIsOpen(fromPath & "PRE.xlsx") Then
Workbooks(fromPath & "PRE.xlsx").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close False
End If

'   Check if WKB is open if open (close). if not continue
If WorkbookIsOpen(fromPath & "POST.xlsx") Then
Workbooks(fromPath & "POST.xlsx").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close False
End If

'   Check if WKB is open if open (close). if not continue
If WorkbookIsOpen(fromPath & "TPOST.xlsx") Then
Workbooks(fromPath & "TPOST.xlsx").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close False
End If

'   Check if WKB is open if open (close). if not continue
If WorkbookIsOpen(fromPath & "TPOST_With.xlsx") Then
Workbooks(fromPath & "TPOST_With.xlsx").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close False
End If

'   Get path from cell B1 on Main tab
fromPath = Sheets("MAIN").Range("B1")

'   Make sure there is a backslash at the end of the from path
If Right(fromPath, 1) <> "\" Then fromPath = fromPath & "\"

Set wkb = ThisWorkbook
Set wkbFrom = Workbooks.Open(fromPath & "ALL.xls")

Application.Calculation = xlManual
Workbooks.Open (fromPath & "PRE.xlsx")

' Run loop and paste to Pre file
i = 1
Do Until Workbooks("COMPARSION").Sheets("Main").Range("Special").Offset(i, 0) = ""
Special = Workbooks("COMPARSION").Sheets("Main").Range("Special").Offset(i, 0)
Workbooks("ALL.xls").Activate
***Sheets("Sec").Activate***
Range("S_VALUE") = Special
RUNALLINSEC
Range("A11:X11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("PRE.xlsx").Activate
With Sheets("PRE").Range("A" & rows.count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With

i = i + 1
Loop

' Close Files no longer needed
Workbooks("PRE.xlsx").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks("ALL.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

' Refresh pivot table with data
Workbooks("COMPARSION").Activate
Sheets("MAIN").Activate
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub
Marco
  • 1
  • 2
  • Normally you don't need to use `Activate` at all... see [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), which is more focused on avoiding `Select` but the same principles apply. – BigBen Aug 27 '20 at 15:00
  • If you could post the remainder of this procedure and the code of the function `WorkbookIsOpen`, I'm sure this could be debugged more easily. An explanation of what the code is supposed to do wouldn't hurt either. – VBasic2008 Aug 27 '20 at 15:12
  • Of course. So really a lot going on but in simplistic terms having a main file we can call A, open another file B (has code), then when B is done copy and paste the data into another file C – Marco Aug 27 '20 at 15:31
  • I'm stuck. Which workbooks are open at the beginning, and which are open in the end? What is the name of `Thisworkbook`? In which workbook is `MAIN` located? Why is workbook `COMPARISON` already open and what's its extension (`.xls`, `.xlsx` or...)? In which workbook is `Sec` located? Is `S_VALUE` a named range in `ALL.xls`? What is `RUNALLINSEC`. Do you have a code for it? Are there more sheets named `Main` or `MAIN` and if not which is it, `Main` or `MAIN`? – VBasic2008 Aug 27 '20 at 17:03
  • So the "buttons" to kickoff the macros are in workbook comparison on sheet main, extension is xlsm. sec sheet is in all workbook. S_value is a named range in all.xls yes. nope just main. and yes RUNALLINSEC is another code running correctly in all.xls. – Marco Aug 27 '20 at 17:26

0 Answers0