• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Open file and copy sheet from newly opened workbook the close.

Hello,

Please help me expand the code below to copy sheet 1 from the newly opened file to the current file and then close the newly opened file.

Thank you


Sub ImportSheet()

        Dim fileName
        fileName = Application.GetOpenFilename("Other Workbook (*.xl*),*.xl*")
        If fileName = "False" Then
        MsgBox "You have not selected a file. Please try again."
        GoTo QuitSub
        End If
        Workbooks.Open fileName:=fileName
        
QuitSub:
End Sub

Open in new window

0
sq30
Asked:
sq30
1 Solution
 
Patrick MatthewsCommented:
If by "current file" you mean the workbook that you are running that code from...


Sub ImportSheet()

        Dim fileName
        Dim wb As Workbook

        fileName = Application.GetOpenFilename("Other Workbook (*.xl*),*.xl*")
        If fileName = "False" Then
        MsgBox "You have not selected a file. Please try again."
        GoTo QuitSub
        End If
        Set wb = Workbooks.Open(fileName:=fileName)
        With ThisWorkbook
            wb.Worksheets(1).Copy After:=.Worksheets(.Worksheets.Count)
            wb.Close False
            .Activate
            .Worksheets(.Worksheets.Count).Select
        End With
        
QuitSub:
End Sub

Open in new window

0
 
sq30Author Commented:
Perfect - Thank you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now