CODEWITHSUNDEEP
excelvba
harry
harry

Reputation: 13

Macro Workbook is getting closed automatically in the middle of execution

I have a code which will read file data from the defined path and copies the data to my Macro workbook's sheet. When I am running the code line by line, it is working perfectly fine. But when I run the entire code, it is getting closed automatically without my permission. Below is my previous code.

Set thisWB = ThisWorkbook
'Open File and Copy Data
Set thatWB1 = Workbooks.Open(TimFilePath)
TFPLR = Cells(Rows.Count, "A").End(xlUp).Row
TFPLC = Cells(1, Columns.Count).End(xlToLeft).Column
TFPLCLTR = Split(Cells(1, TFPLC).Address(True, False), "$")(0)
'MsgBox TFPLCLTR
Range("A2:" & TFPLCLTR & TFPLR).Select
Selection.Copy

'Paste Selected Data in Time Ranges Sheet
'thisWB.Activate
thisWB.Sheets(TimSheet).Activate
If ActiveSheet.AutoFilterMode Then
     ActiveSheet.AutoFilterMode = False
End If
Range("A2").PasteSpecial xlPasteValues

Application.CutCopyMode = False

'Close the File
thatWB1.Close SaveChanges:=False

After I made the below updates, the workbook is still closing.

Set thisWB = ThisWorkbook
'Open Time Range File and Copy Data
Set thatWB1 = Workbooks.Open(TimFilePath)
TFPLR = Cells(Rows.Count, "A").End(xlUp).Row
TFPLC = Cells(1, Columns.Count).End(xlToLeft).Column
TFPLCLTR = Split(Cells(1, TFPLC).Address(True, False), "$")(0)
'MsgBox TFPLCLTR
Range("A2:" & TFPLCLTR & TFPLR).Copy
'Selection.Copy

'Paste Selected Data in Time Ranges Sheet
thisWB.Sheets(TimSheet).Activate
If ActiveSheet.AutoFilterMode Then
     ActiveSheet.AutoFilterMode = False
End If
thisWB.Sheets(TimSheet).Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

'Close the Time ranges File
thatWB1.Close SaveChanges:=False

Upvotes: 0

Views: 314

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

This code should work without relying on Active anything.

Option Explicit 'This line is REALLY important.
'It forces you to declare each variable.
'Tools ~ Options ~ Editor. Tick 'Require Variable Declaration' to
'add it to each new module you create.

Public Sub Test()

    'Set references to required files.
    Dim TimFilePath As String
    TimFilePath = "C:/Somepath/MyFile.xlsx"
    
    Dim thatWB As Workbook
    Set thatWB = Workbooks.Open(TimFilePath)
    
    Dim thatWS As Worksheet
    Set thatWS = thatWB.Worksheets("Sheet1")
    
    Dim thisWB As Workbook
    Set thisWB = ThisWorkbook 'Workbook containing this code.
    
    Dim thisWS As Worksheet
    Set thisWS = thisWB.Worksheets("Sheet1")
    
    'Work on the files without selecting them.
    Dim LastRow As Long
    LastRow = thatWS.Cells.Find("*", , , , xlByRows, xlPrevious).Row
    If LastRow = 0 Then LastRow = 1
    
    Dim LastColumn As Long
    LastColumn = thatWS.Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    If LastColumn = 0 Then LastColumn = 1
    
    Dim LastCell As Range
    Set LastCell = thatWS.Cells(LastRow, LastColumn)

    thatWS.Range("A2", LastCell).Copy
    thisWS.Range("A2").PasteSpecial xlPasteValues
    
    thatWB.Close False

End Sub

Upvotes: 0

Joerg Wood
Joerg Wood

Reputation: 141

Best way to solve this is by declaring a variable to fully control the open workbook in the same way you have for thisWB, eg:

Dim thatWB As Workbook

Set thatWB = Workbooks.Open(TimFilePath)
'do the work
thatWB.Close SaveChanges:=False

Upvotes: 2

Related Questions