CODEWITHSUNDEEP
excelvbaloops
umi
umi

Reputation: 17

Copy several worksheets from a workbook

I want to open a folder and select a file. Then, I would like to copy all the worksheets in the selected file together with its worksheet name into thisworkbook (where the macro is).

If selected file has three worksheets the code below copies the first worksheet three times and does not copy the worksheet name.

Sub Import_File()

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim FileName As String
Dim FilePath As String
Dim WS As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

FileToOpen = Application.GetOpenFilename(Title:="Browse your File")
FilePath = FileToOpen

If FileToOpen <> False Then
    Set OpenBook = Application.Workbooks.Open(FileToOpen)

    For Each WS In OpenBook.Worksheets
    
        Range("A2:XFD1048576").Copy
        ThisWorkbook.Worksheets.Add.Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
   
    Next WS

    OpenBook.Close
    FileName = Dir()
      
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End If

Upvotes: 0

Views: 67

Answers (1)

Rafał B.
Rafał B.

Reputation: 404

Sub Import_File()

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim WS As Worksheet

FileToOpen = Application.GetOpenFilename(Title:="Browse your File")

    If FileToOpen <> False Then
    
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        
        With ThisWorkbook

           For Each WS In OpenBook.Worksheets             
                WS.Copy After:=.Sheets(.Sheets.Count)
           Next WS

        End With

        OpenBook.Close
        
    End If

    With Application
         .ScreenUpdating = True
         .DisplayAlerts = True
    End With    

End Sub

Upvotes: 1

Related Questions