CODEWITHSUNDEEP
excelvba
Forward Ed
Forward Ed

Reputation: 9894

VBA Code just stops in the middle execution with out completing code (NOT A HANG)

I have some code that is copying a upwards of 1K files or so from a network drive to a SharePoint site. When it has completed that task, It calls the following sub. When I step through the code it seems to work fine, and it worked fine on some of the smaller lists I uploaded. However when I just let it run it magically stops executing code and behaves as if everything has successfully been executed. I know it hasn't because The workbook I picked to be open is still open and the workbook I am running the code from is blank.

The best I can surmise is the code stops executing right around:

SourceWB.Sheets("Sheet1").Activate

Because the destination sheet is still blank, I am pretty sure the cells.copy is not executing. The only other thing I can think of is it somehow is grabbing the Destination worksheet as the source work sheet, so nothing actually gets copied and I wind up with a blank sheet. I rule that out though as at the end of my main sub a message box is supposed to appear when things are complete and no message box appears.

Can anyone spot an issue or shed some light on what may be happening. Frustrating that it works when stepping through. (as long as there is a break point afterwards)

Sub ImportIndex()

'Copies Sheet1 from a user selected workbook
'into current work book

    Dim DestinationWS As Worksheet
    Dim DestinationR As Range
    Dim SourceWB As Workbook
    Dim FilenameWB As String

    'clear sheet1 of any previous data/formats etc
    Clear_Worksheet ("Sheet1")

    'Set the location of where the sheet is to be copied to
    Set DestinationWS = ThisWorkbook.Sheets("Sheet1")
    Set DestinationR = DestinationWS.Range("A1")

    'Open the source workbook through file picker
    '****************************************************************
    'Error may occur if workbook is already open
    'Look into how to deal with this in the future
    '****************************************************************
    FilenameWB = Application.GetOpenFilename()

    Set SourceWB = Workbooks.Open(Filename:=FilenameWB)

    'Ensure "sheet1" is the active worksheet
    SourceWB.Sheets("Sheet1").Activate

    'Copies active wrokesheet to Destination
    Cells.Copy DestinationR

    'close the source workbook without saving changes
    SourceWB.Close savechanges:=False

End Sub

I did look at the following question, but it was related to Word. According to one comment, the most recent build seems to have solved their issue.

Clearworksheet function as requested

Sub Clear_Worksheet(Sheetname As String)
'Deletes all cells in the provide worksheet name
'currently will cause an error if the sheet does not exist
    With ThisWorkbook.Sheets(Sheetname)
        .Cells.Delete Shift:=xlUp
        Range("A1").Activate 'probably do no need this activate
    End With

End Sub

I just re ran the code with the elimination of ACTIVATE and also having removed all stepping break points. I also changed the copy line to 'SourceWB.Sheets("Sheet1").Cells.Copy` as suggested. The code still stopped executing after opening the sheet and before copying the sheet to Thisworkbook.

on a side note, I also notice sometimes while stepping through the code and I do a file pick or folder pick, the code seems to terminate unless I have a break point set somewhere slightly after it.

Upvotes: 0

Views: 347

Answers (1)

FAB
FAB

Reputation: 2569

I've commented that not sure why it would stop... having a wild guess it could be because of the very large range you're copying (the whole sheet...).

You should either set the range of what you are copying (cell 1 to last row/column), or in this case, since you are not adding to pre-existent data, could be better off to just copy the sheet.

See if rewriting your code this way would help?

Sub ImportIndex()
'Copies Sheet1 from a user selected workbook
'into current work book

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim SourceWB As Workbook
    Dim FilenameWB As String

    'Open the source workbook through file picker
    On Error Resume Next
    FilenameWB = Application.GetOpenFilename()
    Set SourceWB = Workbooks.Open(Filename:=FilenameWB)
    On Error GoTo 0

    If Not SourceWB Is Nothing Then
        'ws.Name = "something else" 'rename this if you want to keep "Sheet1" name from the source workbook
        SourceWB.Sheets("Sheet1").Copy After:=ws
        ws.Delete

        'close the source workbook without saving changes
        SourceWB.Close savechanges:=False
    Else
        'Some error handling here... msgbox/debug.print etc
    End If

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

End Sub

PS: Note that there are various ways to handle opening a workbook, but for simplicity reasons, this should work just fine.

Upvotes: 1

Related Questions