import a sheet from a closed workbook from location in cell

[KlausW]

Hi I am using this VBA code to import a sheet from a closed workbook. It works. But I would like that the location and file name came from cell D1.
I've tried redoing this but it doesn't work.

Set sourceWorkbook = Workbooks.Open("D:\DNBR 2024\Delinger 2024\Delingsliste MASTER.xlsm") to Set sourceWorkbook = Workbooks.Open Range ("D1").Value.

Some who can help, any help would be appreciated.

Best regarts Klaus W

Sub Rektangelafrundedehjørner5_Klik()

 ' Declare variables
    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim ws As Worksheet
    
    ' Set the destination workbook to the active workbook
    Set targetWorkbook = ThisWorkbook
    
    ' Disbales screen updating
    Application.ScreenUpdating = False
    
    ' Open the source workbook
    Set sourceWorkbook = Workbooks.Open("D:\DNBR 2024\Delinger 2024\Delingsliste MASTER.xlsm")
    
    ' Set the worksheet that you want to copy
    Set ws = sourceWorkbook.Sheets("SkibsNr")
    
    ' Copy the worksheet to the destination workbook
    ws.Copy After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
    
    ' Close the source workbook without saving changes
    sourceWorkbook.Close SaveChanges:=False
    
    ' Enables screen updating
    Application.ScreenUpdating = True
    
    ' Release the object variables to free up memory
    Set ws = Nothing
    Set sourceWorkbook = Nothing
    Set targetWorkbook = Nothing

   
End Sub

 

[bobsan42]

Change sheet_name_here to the actual sheet name and try the line below:

Set sourceWorkbook = Workbooks.Open (ThisWorkbook.Worksheets("sheet_name_here").Range ("D1").Value)

Depending on how you start the procedure, this may also work:

Set sourceWorkbook = Workbooks.Open (ActiveSheet.Range ("D1").Value)

 

[KlausW]

Change sheet_name_here to the actual sheet name and try the line below:

Set sourceWorkbook = Workbooks.Open (ThisWorkbook.Worksheets("sheet_name_here").Range ("D1").Value)

Depending on how you start the procedure, this may also work:

Set sourceWorkbook = Workbooks.Open (ActiveSheet.Range ("D1").Value)

Hi bobsan42
Just as it should be.
Many thanks.
Best regards Klaus W

 

[bobsan42]

You're welcome.