VBA works perfectly for Excel 2016, but not 2013...any ideas?

[RMXByker]

Hey everyone, I have a workbook that is designed to only be utilized by the user through the userform. Application visibility is off during the entire process and then closes excel when closing the userform. Works perfectly on my and a coworkers Excel 2016 but doesn't seem to get through all the code in Excel 2013. The code just seems to stop for no apparently reason and not reload the userform and hide the application after clearing all the cells. Any ideas?

Workbook

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    MsgBox "Sorry, you cannot save changes to this workbook!"
    Cancel = True
End Sub


Private Sub Workbook_Open()
    
    Application.Visible = False
    PopUp.Show
    
End Sub

Module Code

Sub ImportData()

'Pull Data from other source workbook
    Application.Visible = False
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Set wkbCrntWorkBook = ActiveWorkbook
    Application.ScreenUpdating = False
    Workbooks.Open (Range("A1"))
    Set wkbSourceBook = ActiveWorkbook
    Dim CSVName As Variant
    CVSName = Dir(ActiveWorkbook.Name)
    Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set rngSourceRange = Range("A1:D" & lrow)
    wkbCrntWorkBook.Activate
    Set rngDestination = Range("A1")
    rngSourceRange.Copy rngDestination
    wkbSourceBook.Close False
    
'Remove unnecessary columns
    Range("B:B,D:D").Delete
            
'Remove special characters from Torque values
    Cells.Replace What:="00;", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


'Set column Titles
    Range("A9").Value = "Angle (Deg)"
    Range("B9").Value = "Torque (Nm)"
    Columns("A:B").AutoFit
    
'Find how many degs of data we have and set as value
    Dim Degs As String
    Dim NoDegs As String
    Dim NoRows As Long
    Degs = Range("A8").Value
    NoDegs = Replace(Degs, "Max. Total Angle;", "") 'Limitation of VBA
    Range("B8").Value = NoDegs
    Range("B8").NumberFormat = "#,##0"
    NoRows = Application.Sum(Range("b8"), 10) 'Limitation of VBA
             
'Graph
    'add scatter plot
    Application.ScreenUpdating = False
    Sheets("Chart1").Select
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A9:B" & NoRows)
    
'Remove.CSV text from name and saveas for file
    NameofFile = Left(CVSName, Len(CVSName) - 4)
        ActiveWorkbook.Sheets.Copy 'creates new workbook without macros"
            'The New workbook copy is now the Active workbook
        ActiveWorkbook.SaveAs FileName:=NameofFile, FileFormat:=51
        ActiveWorkbook.Close
        Application.ScreenUpdating = True
        
'Reopen Workbook
        Dim sPath As String
        Dim sName As String
        Application.Visible = False
        sName = ThisWorkbook.Name
        sPath = ThisWorkbook.Path
        ThisWorkbook.Saved = True
        Workbooks.Open FileName:=sPath & "\" & sName
        


'Clear Out Old Data
        ActiveChart.ChartArea.Select
        ActiveChart.PlotArea.Select
        ActiveChart.FullSeriesCollection(1).Delete
        Sheets("Sheet1").Select
        Columns("A:Z").Select
        Selection.ClearContents
        Range("A1").Select
        PopUp.TextBox1.Text = ""
        PopUp.Repaint
        
        MsgBox ("Save completed,ready to convert the next file"), vbInformational


End Sub

 

[RMXByker]

Surprised no one has come across this before. Still tinkering but haven't found a resolution.

 

[RMXByker]

Still no one has any ideas...I think I'm going to have to put my laptop to the firing squad...lol.

 

[mrshl9898]

I've had a few weird things on colleagues PCs, have you tried making it visible, allowing screen updates then stepping through the code?

 

[RMXByker]

I did and nothing seemed to be really noticeable. Just stops for no known reason. I had our VBA expert here at work take a gander and he didn't notice anything either. I think tomorrow and I may see the differences in add-ins and what not between the 2 versions here at the office and start tracking things down that way.

 

[mrshl9898]

Would be glad to hear any findings, good luck!

 

[RMXByker]

I was able to come to a solution by removing the section of code labeled above at 'Reopen Workbook. I then proceeded to clean up some more of things and add some titles as well. Feel free to see below;

Sub ImportData()

'Pull Data from other source workbook
    Application.Visible = False
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Set wkbCrntWorkBook = ActiveWorkbook
    Application.ScreenUpdating = False
    Workbooks.Open (Range("A1"))
    Set wkbSourceBook = ActiveWorkbook
    Dim CSVName As Variant
    CSVName = Dir(ActiveWorkbook.Name)
    Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set rngSourceRange = Range("A1:D" & lrow)
    wkbCrntWorkBook.Activate
    Set rngDestination = Range("A1")
    rngSourceRange.Copy rngDestination
    wkbSourceBook.Close False
    
'Remove unnecessary columns
    Range("B:B,D:D").Delete
            
'Remove special characters from Torque values
    Cells.Replace What:="00;", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


'Set column Titles
    Range("A9").Value = "Angle (Deg)"
    Range("B9").Value = "Torque (Nm)"
    Columns("A:B").AutoFit
    
'Find how many degs of data we have and set as value
    Dim Degs As String
    Dim NoDegs As String
    Dim NoRows As Long
    Degs = Range("A8").Value
    NoDegs = Replace(Degs, "Max. Total Angle;", "") 'Limitation of VBA
    Range("B8").Value = NoDegs
    Range("B8").NumberFormat = "#,##0"
    NoRows = Application.Sum(Range("b8"), 10) 'Limitation of VBA
             
'Graph
    'add scatter plot
    Application.ScreenUpdating = False
    Sheets("Chart1").Select
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A9:B" & NoRows)
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Angle (deg)"
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Torque (Nm)"
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    
    Dim ChartTitle As Variant
    ChartTitle = CSVName
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = Left(CSVName, Len(CSVName) - 4)
    
'Remove.CSV text from name and saveas for file
    NameofFile = Left(CSVName, Len(CSVName) - 4)
    ActiveWorkbook.Sheets.Copy 'creates new workbook without macros"
        'The New workbook copy is now the Active workbook
    ActiveWorkbook.SaveAs Filename:=NameofFile, FileFormat:=51
    
'Save the Chart as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NameofFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    ActiveWorkbook.Close
 
'Clear Out Old Data
    Application.Visible = False
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select
    ActiveChart.FullSeriesCollection(1).Delete
    Sheets("Sheet1").Select
    Columns("A:Z").Select
    Selection.ClearContents
    Range("A1").Select
    PopUp.TextBox1.Text = ""
      
    MsgBox ("Save completed,ready to convert the next file"), vbInformational


End Sub