MS EXCEL VBA - I need to import a worksheet from one excel file to another

By : Larry
Source: Stackoverflow.com
Question!

I need to import a worksheet from one excel workbook (worksheet name is not always the same) and import it into the current active workbook.

Here is what I have so far:

Sub openFile_Click()
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a Report to Parse", _
    FileFilter:="Report Files *.rpt (*.rpt),")

    If FileToOpen = False Then
        MsgBox "No File Specified.", vbExclamation, "ERROR"
        Exit Sub
    Else
        Workbooks.Open Filename:=FileToOpen

        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Set wb1 = ActiveWorkbook
        wb2 = Workbooks(FileToOpen)     'This is where I am stuck..I can't give it a static name

        For Each Sheet In wb1.Sheets
            If Sheets.Visible = True Then
                Sheets.Copy After:=wb2.Sheets(wb2.Sheets.Count)
            End If

        Next Sheet

    End If
By : Larry


Answers
This code will work for what you want you want. I made the following corrections.

  1. Move all declarations of variables to beginning of procedure so they are declared before you use them. It is just good practice.

  2. Assign your Active Workbook to the variable before you open the second workbook so there is only one workbook open.

  3. Your for each statement had a few corrections as well.

    Sub openFile_Click()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    
    Set wb1 = ActiveWorkbook
    
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a Report to Parse", _
    FileFilter:="Report Files *.rpt (*.rpt),")
    
    If FileToOpen = False Then
        MsgBox "No File Specified.", vbExclamation, "ERROR"
        Exit Sub
    Else
        Set wb2 = Workbooks.Open(Filename:=FileToOpen)
    
        For Each Sheet In wb2.Sheets
            If Sheet.Visible = True Then
                Sheet.Copy After:=wb1.Sheets(wb1.Sheets.Count)
            End If
        Next Sheet
    
    End If
    
    End Sub
    


Set the Workbook on open, (or set the workbook later without the filepath)

Here you go:

Sub openFile_Click()
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.rpt (*.rpt),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Set wb1 = ActiveWorkbook
    Set wb2 = Workbooks.Open(FileToOpen)




    For Each Sheet In wb1.Sheets
        If Sheet.Visible = True Then
            Sheets.Copy After:=wb2.Sheets(wb2.Sheets.Count)
        End If

    Next Sheet

End If
End Sub
By : Abe Gold


This video can help you solving your question :)
By: admin