Name of sheets in cell to import

[KlausW]

Hi
I have a challenge with this VBA code.
I would like to be able to change the name of the sheets I would like to import.
I can get it to run if I change the line
With ActiveWorkbook.Sheets.Nanme = Range("C1").Value
To
With ActiveWorkbook.Sheets("IBM")
The name of the sheet I would like to be imported is in C1
Any help will be appreciated
Best Regards
Klaus W

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.Name = Range("C1").valu
        .Copy After:=ThisWorkbook.Sheets(1)
     End With

Workbooks(Filename).Close

Filename = Dir()
Loop

End Sub

 

[ebea]

You have two typo failures.

With ActiveWorkbook.Sheets.Nanme = Range("C1").Value
Has to be: Name
And in your code:
With ActiveWorkbook.Sheets.Name = Range("C1").valu
...are missing an e after .Valu

 

[StephenCrump]

If C1 contains a sheet name, then the correct syntax would be:

With ActiveWorkbook.Sheets(Range("C1").Value)

But rather than relying on which workbook is active, you could open the workbook like this. Then you can subsequently refer to wbOpen or ThisWorkbook, and it will always be clear which workbook you mean.

Dim wbOpen As Workbook

'...

Set wbOpen = Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True)

You should also appropriately qualify the (currently unqualified) reference to Range("C1")

 

[KlausW]

You have two typo failures.

With ActiveWorkbook.Sheets.Nanme = Range("C1").Value
Has to be: Name
And in your code:
With ActiveWorkbook.Sheets.Name = Range("C1").valu
...are missing an e after .Valu

Thanks, I fixed it. But it doesn't do what I want.

 

[KlausW]

Thanks, I fixed it. But I can't get it to do that.

Sub Rektangelafrundedehjørner1_Klik()

Dim Path As String
Dim Filename As String
Dim wbOpen As Workbook

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(Range("C1").Value)
        .Copy After:=ThisWorkbook.Sheets(1)
     End With
Set wbOpen = Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True)
Workbooks(Filename).Close

Filename = Dir()
Loop

End Sub

 

[StephenCrump]

Try this:

Sub Rektangelafrundedehjørner1_Klik()

    Dim Path As String
    Dim Filename As String, SheetName As String
    
    Path = Range("A1").Value
    SheetName = Range("C1").Value
    Filename = Dir(Path & "*.xlsm")
        
    Do While Filename <> ""
        With Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True)
            .Sheets(SheetName).Copy After:=ThisWorkbook.Sheets(1)
            .Close SaveChanges:=False
        End With
        Filename = Dir()
    Loop

End Sub

 

[KlausW]

Hi, it runs well, there is a sheet with the same name (The name is in C1) it must be retrieved from 6 different files in the same folder. The VBA code only takes from a file. Coming up with errors in this line.

Regards Klaus

.Sheets(SheetName).Copy After:=ThisWorkbook.Sheets(1)

 

[KlausW]

Try this:

Sub Rektangelafrundedehjørner1_Klik()

    Dim Path As String
    Dim Filename As String, SheetName As String
  
    Path = Range("A1").Value
    SheetName = Range("C1").Value
    Filename = Dir(Path & "*.xlsm")
      
    Do While Filename <> ""
        With Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True)
            .Sheets(SheetName).Copy After:=ThisWorkbook.Sheets(1)
            .Close SaveChanges:=False
        End With
        Filename = Dir()
    Loop

End Sub

Hi StephenCrump, it was my mistake, a mistake 40 as we say in Denmark. 40 cm from the body. The code you made runs as it should. Thank you very much. Best regards from Klaus in Denmark

 

[StephenCrump]

... it was my mistake, a mistake 40 as we say in Denmark.

Ha ha! No problem. I'm glad you got it working.