import a sheet with name standing in a cell from another Workbook

[KlausW]

Hi

I use this VBA code to import sheets from another Workbook. There is only one sheet so it is easy. Now I would like to import a sheet with name standing in a sheet “Stamdata” cell A1 from another Workbook that contains many sheets with different names. Some who can help.

Any help will be appreciated

Best regards Klaus W

Sub Rektangelafrundedehjørner1_Klik()

Dim Path As String
Dim Filename As String
Dim Sheet As Worksheet

Path = Range("a1").Value
Filename = Dir(Path & "*.xlsm")
Do While Filename <> ""

Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

    For Each Sheet In ActiveWorkbook.Sheets
        Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet

Workbooks(Filename).Close

Filename = Dir()
Loop

End Sub

 

[abdelfattah]

Hi,
I'm not sure if I can help you .but you explanation is not clear for me
I suppose the path in A1 and you tell us the sheet name is in A1, how is it?!
I think you should say copy specific sheet from file to another based on path into cell .
this is my try

Sub Rektangelafrundedehjørner1_Klik()

Dim Path As String
Dim Filename As String


Path = Range("a1").Value
Filename = Dir(Path & "*.xlsm")
Do While Filename <> ""

Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
'On Error Resume Next
    With ActiveWorkbook.Sheets("stock")
        .Copy After:=ThisWorkbook.Sheets(1)
     End With

Workbooks(Filename).Close

Filename = Dir()
Loop

End Sub

if there are multiple files , then will show error . in this case you should enable the disabled line to skip error.
I believe there is better way to avoid error , but I have no idea how do that . maybe experts guide us .
anyway you said one file so the error doesn't show .
good luck

 

[KlausW]

Hi,
I'm not sure if I can help you .but you explanation is not clear for me
I suppose the path in A1 and you tell us the sheet name is in A1, how is it?!
I think you should say copy specific sheet from file to another based on path into cell .
this is my try

Sub Rektangelafrundedehjørner1_Klik()

Dim Path As String
Dim Filename As String


Path = Range("a1").Value
Filename = Dir(Path & "*.xlsm")
Do While Filename <> ""

Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
'On Error Resume Next
    With ActiveWorkbook.Sheets("stock")
        .Copy After:=ThisWorkbook.Sheets(1)
     End With

Workbooks(Filename).Close

Filename = Dir()
Loop

End Sub

if there are multiple files , then will show error . in this case you should enable the disabled line to skip error.
I believe there is better way to avoid error , but I have no idea how do that . maybe experts guide us .
anyway you said one file so the error doesn't show .
good luck

Thank allot

 

[KlausW]

sorry it's a mistake the Path is in cell A1 an sheets name in C1.
Is it possible to insert a Range cell C1 in this line.

With ActiveWorkbook.Sheets("stock")

 

[abdelfattah]

well,
I hope the experts show us , sorry!

 

[KlausW]

well,
I hope the experts show us , sorry!

Thanks allot it works Regards Klaus W