Solved

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

Posted on 2013-02-03
2
261 Views
Last Modified: 2013-02-03
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
Comment
Question by:sq30
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38849173
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
 

Author Closing Comment

by:sq30
ID: 38849213
Perfect - Thank you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question