Switch Back to Workbook Opened via File Browser

[AaronJ313]

Hi all,

I am extremely new to VBA and have never posted before, as I have been able to get everything I needed from others' questions.However, I have come up with an issue that I have been unable to solve. Sorry if it has already been answered in another thread and I was unable to find it!

From my main workbook, my macro is currently able to open another Excel workbook using File Browser, copy a selection of cells from that workbook (based on the contents of a cell in the original workbook), and finally switch back to the original workbook and paste those cells. The last thing I want to be able to do is close the second workbook (where the cell range was copied from) but I have been unable to figure it out. My current code is pasted below.

Can anyone help me with this? Thanks in advance!

Sub IndustryImport()
'
' IndustryImport Macro
'
Industry = Worksheets("Data").Range("B3")
Dim wb As Workbook
Set wb = ThisWorkbook
FileName = Application.GetOpenFilename
If FileName = False Then Exit Sub
Workbooks.Open (FileName)
Worksheets(Industry).Activate
Range("C4:C204").Select
Selection.Copy
ThisWorkbook.Activate
Worksheets("Data").Activate
Range("C21").Select
Selection.PasteSpecial Paste:=xlPasteValues


' ActiveWorkbook.Close (False)
End Sub

 

[Jerry Sullivan]

Hi Aaron and Welcome to the Board,

Using your existing code you could assign a reference to the second workbook to the variable wb, then use that to Close the workbook.

Sub IndustryImport()
    Industry = Worksheets("Data").Range("B3")
    Dim wb As Workbook
 '   Set wb = ThisWorkbook  '--not used in OP code
    Filename = Application.GetOpenFilename
    If Filename = False Then Exit Sub
    [B][COLOR="#0000CD"]Set wb =[/COLOR][/B] Workbooks.Open(Filename)
    Worksheets(Industry).Activate
    Range("C4:C204").Select
    Selection.Copy
    ThisWorkbook.Activate
    Worksheets("Data").Activate
    Range("C21").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    [B][COLOR="#0000CD"]wb.[/COLOR][/B]Close (False)
 End Sub

You can streamline your code by reducing or eliminating the use of ActiveCell, Select, Selection and instead just reference the ranges directly without selecting them.

Option Explicit

Sub IndustryImport2()
    Dim Industry As String
    Dim wb As Workbook
    Dim Filename As Variant

    
    Industry = Worksheets("Data").Range("B3")
    Filename = Application.GetOpenFilename
    If Filename = False Then Exit Sub

    
    Set wb = Workbooks.Open(Filename)
    wb.Worksheets(Industry).Range("C4:C204").Copy
    ThisWorkbook.Worksheets("Data").Range("C21").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = xlCopy
    wb.Close (False)
End Sub

 

[AaronJ313]

Hi Jerry,

Thanks for solving this for me! I knew there was a relatively simple solution. I also really appreciate you taking the time to make recommendations on streamlining my code. I knew it was probably much sloppier than it needed to be. I can take your recommendations and apply them to my other macros!

Thanks again!

Aaron