Only to import the sheet with the name in Range Q1

[KlausW]

I use this code to import sheets with dialog box, it runs optimally, but I wish Excel only to import the sheets whit the name in Range Q1.

Any help will be much appreciated.
Many Thanks

Klaus W

Sub Rektangelafrundedehjørner1_Klik()

 Dim ws As Worksheet
   Dim Wbk As Workbook
   Dim Pth As String, Fname As String
      Pth = GetFolder()
      Fname = Dir(Pth & "\*.xlsm")
   
   Do While Fname <> ""
     Set Wbk = Workbooks.Open(Pth & "\" & Fname)
      For Each ws In Wbk.Worksheets
         If Not ShtExists(ws.Name, ThisWorkbook) Then
            ws.Copy , ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
         End If
      Next ws
      Wbk.Close False
      Fname = Dir
   Loop

End Sub

Function GetFolder() As String
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
'dlg.InitialFileName = "c:\"
'If dlg.Show = -1 Then
'GetFolder = dlg.SelectedItems(1)
'End If
With dlg

    .InitialFileName = "c:\"
    .Show
    GetFolder = .SelectedItems(1)
   
End With

End Function

​

 

[Fluff]

Do you mean to check each sheet of each workbook & then import that sheet if Q1= a certain value?
Or do you mean that you have a sheet somewhere where Q1 has the name of the sheet to be imported?

 

[KlausW]

I mean that I have a sheet somewhere where Q1 has the name of the sheet to be imported?

 

[Fluff]

In that case what is the name of the sheet & is it in the same workbook as the code?

 

[KlausW]

The Sheet I want to import is called "Address List Fast and VPL",
I will insert the name in cell Q1.
No it's not in the same workbook, that's why I use a dialog box to find the file. That does not need to be changed.
KW

 

[Fluff]

I will insert the name in cell Q1.

In what sheet?

 

[KlausW]

Is it the name of the Sheet where the code shut be(Name: Delingsliste 2020 med billeder 10-11-2020) , ore the name of the workbook where the sheet shout be importet from(Name: Adresse fastbesætning og VPL 2021)?

 

[Fluff]

If you are going to put the name of the sheet to be imported into Q1, I need to know which sheet that cell is located in.

 

[KlausW]

OK now I see, the Sheet the Q1 is located in is Named: Stamdata

 

[Fluff]

Ok, how about

Sub Rektangelafrundedehjørner1_Klik()

 Dim ws As Worksheet
   Dim Wbk As Workbook
   Dim Pth As String, Fname As String, ShtName As String
      Pth = GetFolder()
      Fname = Dir(Pth & "\*.xlsm")
      ShtName = LCase(Sheets("Stamdata").Range("Q1").Text)
   
   Do While Fname <> ""
     Set Wbk = Workbooks.Open(Pth & "\" & Fname)
      For Each ws In Wbk.Worksheets
         If LCase(ws.Name) = ShtName Then
            ws.Copy , ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
         End If
      Next ws
      Wbk.Close False
      Fname = Dir
   Loop

End Sub