Solved

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

Posted on 2013-02-03
2
253 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

774 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