CODEWITHSUNDEEP
excelvba
jmten
jmten

Reputation: 49

Search for and Copy Worksheets from one Workbook to another

I have a workbook (wb1) which contains the macro. I open a file (wb) by selecting it in a msgbox.

I want to loop through all sheets of wb and if sheet.name like "xxx" save a copy of the sheet in wb1 before the first sheet.

Sub GetFile()

    Dim fNameAndPath As Variant, wb As Workbook
    Dim wb1 As ThisWorkbook

    fNameAndPath = Application.GetOpenFilename(FileFilter:="All Files (*.*),*.*", _
      Title:="Sélectionner le fichier des extractions Magnitude sur la Phase IAS CONSO")

    If fNameAndPath = False Then Exit Sub

    Set wb = Workbooks.Open(fNameAndPath)

    For Each Sheet In wb.Sheets

        If LCase(Sheet.Name) Like "*Sheet1*" Then
            Range("A2").Select
            'ActiveSheet.Delete
        End If

    Next Sheet

Upvotes: 0

Views: 60

Answers (1)

user3598756
user3598756

Reputation: 29421

try this:

Option Explicit

Sub GetFile()
    Dim fNameAndPath As Variant
    Dim sht As Worksheet

    fNameAndPath = Application.GetOpenFilename(FileFilter:="All Files (.),.", Title:="Sélectionner le fichier des extractions Magnitude sur la Phase IAS CONSO")
    If fNameAndPath = False Then Exit Sub

    Set wb = Workbooks.Open(fNameAndPath)
    For Each sht In wb.Sheets
        If LCase(sht.Name) Like "*sheet1*" Then sht.Copy Before:=ThisWorkbook.Sheets(1)
    Next sht
    wb.Close False '<--| close the newly opened workbook without saving it
End Sub

also note I changed:

If LCase(sht.Name) Like "*Sheet1*"

to:

If LCase(sht.Name) Like "*sheet1*"

otherwise the check would never return True since it'd compare an all lowercase characters string (LCase(sht.Name)) to a string with one uppercase character ("S")

Upvotes: 0

Related Questions