[Resolved] VBA only partial run first time, then fully executes the second time... Why?!?

[TTUK]

Hello,

I have created a vba script from combining 3 different modules in to one.
In a nut-shell it exports some data to a register going to lastrow, then creates a unique folder with name, then places a copy of the worksheet in that specific folder.

However, the first time it run, it gets to the point where it creates the folder and doesn't completed by adding saving the file in the folder.
Yet, the second time I run the script it does everything.

Is there something in my code which I cannot see!?

Public Sub Groupall()    
    Dim lastRow         As Long
    Dim r               As Range
    Dim ws              As Worksheet
    Dim sMainFolder     As String
    Dim sDestFolder     As String
    Dim sCAR            As String
    Static Path         As String
    Static filename     As String
    
    Application.ScreenUpdating = False
    
    Set r = Worksheets("Export").Range("A2:L2")
    Set r2 = Worksheets("CAR Register").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
    r.Copy
    r2.PasteSpecial xlValues
    
    Sheet5.Activate
    ActiveCell.Offset(0, -1).Select
    ActiveCell.Select
    Selection.Copy
    Sheet6.Activate
    Sheets("Raise CAR").Range("B1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
    
    
    sMainFolder = "\\dataserver\ManagementSystem\NYL-Health, Safety & Environmental\7. Corrective Action Requests (CAR)\"
    
    If Len(Dir(sMainFolder, vbDirectory)) = 0 Then
        MsgBox "The main folder does not exist.", vbCritical, "Main folder?"
        Exit Sub
    End If
    
    If Right(sMainFolder, 1) <> "\" Then sMainFolder = sMainFolder & "\"
    
    sCAR = Sheet6.Range("AD2").Value
    
    If Len(sCAR) = 0 Then
        MsgBox "The CAR is missing", vbExclamation, "Missing?"
        Exit Sub
    End If
    
    If Len(Dir(sMainFolder & "\" & sCAR, vbDirectory)) = 0 Then
        If Len(Dir(sMainFolder & sCAR, vbDirectory)) = 0 Then
            MkDir sMainFolder & sCAR
        End If
        
    End If
    
    
    'Dim wsheet As Worksheet
    
    If Len(Path) = 0 Then
        Path = Sheet6.Range("AD2")
    Else
        filename = Sheet6.Range("AB4")
        
        'Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        
        ActiveSheet.Copy   'not sure why you're doing this, but do so if it makes sense elsewhere in your code
        With ActiveWorkbook.ActiveSheet
            '.Range("42:" & Rows.Count).EntireRow.DeletexlShiftDown
            '.Range(.Cells(1, "J"), .Cells(1, Columns.Count)).EntireColumn.Delete xlToRight
            
            .Parent.SaveAs filename & ".xls"
            
            ActiveWorkbook.Theme.ThemeColorScheme.Load ( _
            "\\dataserver\ManagementSystem\Artwork, Images & Icons\VBA_Colour Themes\Office 2016.xml" _
            )
            
            .Parent.Close False
        End With
        Path = ""
        filename = ""
    End If
    
End Sub

The part of the code is forgets to run each time is this bit...

    'Dim wsheet As Worksheet    
    If Len(Path) = 0 Then
        Path = Sheet6.Range("AD2")
    Else
        filename = Sheet6.Range("AB4")
        
        'Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        
        ActiveSheet.Copy   'not sure why you're doing this, but do so if it makes sense elsewhere in your code
        With ActiveWorkbook.ActiveSheet
            '.Range("42:" & Rows.Count).EntireRow.DeletexlShiftDown
            '.Range(.Cells(1, "J"), .Cells(1, Columns.Count)).EntireColumn.Delete xlToRight
            
            .Parent.SaveAs filename & ".xls"
            
            ActiveWorkbook.Theme.ThemeColorScheme.Load ( _
            "\\dataserver\ManagementSystem\Artwork, Images & Icons\VBA_Colour Themes\Office 2016.xml" _
            )
            
            .Parent.Close False
        End With
        Path = ""
        filename = ""
    End If

I really do hope someone can help me out with this?

Thank you very much,

 

[TTUK]

Re: VBA only partial run first time, then fully executes the second time... Why?!?

After the 30-seconds of me posting this I have figured out what my error was.

It was this IF statement -

    If Len(Path) = 0 Then        
Path = Sheet6.Range("AD2")
    Else

The (Path) would always = a value when executed and not comeback with a 0, this was making the code miss a step each time.

Please make this as resolved.


Thanks,