CODEWITHSUNDEEP
vbaexcel
Tejkaran Samra
Tejkaran Samra

Reputation: 96

Excel closes both workbooks, instead of just one

I have Workbook 1 running the macro/VBA

The file location of workbook 2 is saved in a sheet on workbook 1, which is opened

Information is copied from workbook 2 into workbook 1 Close workbook 2

Repeat with multiple workbooks

Finish

However, at random intervals, excel closes all the files, without saving. My gut feeling is there is some kind of mix up in which workbook it is reading and therefore it closes all the workbooks.

Is there some kind of methodology I am doing wrong, should i change something somewhere?

My code is as below:

Option Explicit
Sub Test_macro()

Application.ScreenUpdating = False

'General Variables
Dim Title           As String    'Title it is looking for
Dim Finder          As Range     'Help with titles
Dim Chosen          As String    'The chosen area to be viewed
Dim Offsetter       As Integer   'Help with offset chosen value

'Coying of stuff from other workbook into this one
Dim workB1          As Workbook  'This workbook
Dim workB2          As Workbook  'Where I will copy from
Dim sourceColumn    As Range     'Range from the budget pack
Dim targetColumn    As Range     'Range to be pasted in here
Dim copyColumn      As Variant   'Columns to be copied
Dim columnCount     As Integer   'Value of loop
copyColumn = Array("D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R")

'For looping through all the workbooks
Dim x               As Integer
Dim workbookCount   As Integer
Dim Placer          As Integer

'Set file name for this workbook
Set workB1 = ThisWorkbook

'Clear information
Sheets("Selection").Columns("D:S").Clear

Sheets("Lookup").Select
Range("H4").Select
workbookCount = Range(ActiveCell, ActiveCell.End(xlDown)).Count

For x = 0 To workbookCount - 1

    'Clear information
    Sheets("DataPaste").Columns("D:R").Clear

    'Check the file exists
    If Not Dir(Sheets("Lookup").Range("H4").Offset(x, 0).Value & Sheets("Lookup").Range("I4").Offset(x, 0).Value) = vbNullString Then

        'these rows show what sheet it is referring to
        Application.ScreenUpdating = True
        Sheets("Selection").Select
        Sheets("Selection").Range("E3") = Sheets("Lookup").Range("H4").Offset(x, 0).Value & Sheets("Lookup").Range("I4").Offset(x, 0).Value
        Application.ScreenUpdating = False

        'Open and set Name of other workbook
        Workbooks.Open Filename:=Sheets("Lookup").Range("H4").Offset(x, 0).Value & Sheets("Lookup").Range("I4").Offset(x, 0).Value
        Set workB2 = Workbooks(workB1.Sheets("Lookup").Range("I4").Offset(x, 0).Value)
        'workB2.Activate
        workB1.Activate

        'Copy into this file, columns are labelled in the array-make sure columnCount matches array count
        Do Until columnCount >= 15
            Set sourceColumn = Workbooks(Sheets("Lookup").Range("I4").Offset(x, 0).Value).Worksheets(Sheets("Selection").Range("B2").Value).Columns(copyColumn(columnCount))
            Set targetColumn = Workbooks("Macro to get budget lines V3").Worksheets("DataPaste").Columns(copyColumn(columnCount))
            sourceColumn.Copy Destination:=targetColumn
            columnCount = columnCount + 1
        Loop

        'Close the second workbook
        workB2.Close SaveChanges:=False

        'Copy and paste it onto the correct tab
        Chosen = Sheets("Selection").Range("B3")
        Sheets("DataPaste").Select
        Columns("D:D").Select
        Cells.Find(Chosen).Select

        'If cell is what we want, copy and paste, go down one cell and loop
        Do Until ActiveCell.Value = ""
            If ActiveCell.Value <> "" Then
                Sheets("DataPaste").Rows(ActiveCell.Row).EntireRow.Copy
                Sheets("Selection").Select
                Sheets("Selection").Range("A5").Offset(Placer, 0).Select
                Sheets("Selection").Paste

                Sheets("Selection").Range("B5").Offset(Placer, 17) = Sheets("Lookup").Range("I4").Offset(x, 0).Value

                Sheets("DataPaste").Select
                Columns("D:D").Select
                Cells.Find(Chosen).Offset(Offsetter, 0).Select
            End If
            ActiveCell.Offset(1, 0).Select
            Offsetter = Offsetter + 1
            Placer = Placer + 1
        Loop

    'If the workbook does not exist in the folder then alert people to it
    Else
    MsgBox (Sheets("Lookup").Range("I4").Offset(x, 0).Value) & " Does not exist"

    End If

    'Reset Variables
    columnCount = 0
    Offsetter = 0

'Go onto the next workbook
Next x

'End operation
Sheets("Selection").Select
Range("A1").Select
Sheets("Selection").Columns("T:V").Clear
MsgBox "All Done"

Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 67

Answers (1)

Vityata
Vityata

Reputation: 43585

workB2.Close is what closes your workbook.

This is how you set the workB2:

Set workB2 = Workbooks(workB1.Sheets("Lookup").Range("I4").Offset(x, 0).Value)

You do this in a loop - For x = 0 To workbookCount - 1. Thus, most probably the code sets a workbook to workB2, it closes it, then it sets workB2 to another workbook and it closes it again. Check this range to be sure:

workB1.Sheets("Lookup").Range("I4").Offset(x, 0).Value

Edit:

To realize better what is happening, change this line:

workB2.Close with this code:

MsgBox workB2.Name
Stop
workB2.Close

When the program stops, take a look at the range.

Upvotes: 1

Related Questions