Cell reference file path

[Fredek]

Hi guys,

Short question today, in cell A2 I have the file I want the VBA to open:[TABLE="width: 749"]
<tbody>[TR]
[TD][TABLE="width: 749"]
<tbody>[TR]
[TD][TABLE="width: 749"]
<tbody>[TR]
[TD]
e.g. C:\Users\user1\Desktop\Book1.xlsx

How do I go about using information in cell A2 to open the file? I know it has to be Workbooks.Open Filename... but what comes next?

Workbooks.Open Filename:=

Thank you!

[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Fredek]

Hi Fluff, added the variable "Set StrtSht = ActiveSheet", now I get the runtime error... Apologies Fluff, I'm a total beginner..

Sub Tmp()
    
    Dim SrcWbk As Workbook
    Dim StrtSht As Worksheet
    
    
    Set StrtSht = ActiveSheet
     Worksheets("GMCR Extract").ListObjects("GMCR_tbl").AutoFilter.ShowAllData
     
     Set SrcWbk = Workbooks.Open(Filename:=StrtSht.Range("U3").Value)
   
    
    With SrcWbk.Sheets("Tbl_M_GMCR_Daily_Positions")
        .Range("A1", .Range("R" & Rows.Count).End(xlUp)).Copy _
             Workbooks("Book1.xlsm").Worksheets("GMCR Extract").Range("A2")
    End With
End Sub

 

[Fluff]

Is the file you're opening an .xls file?

 

[Fredek]

When I try this:

Sub Tmp()
    
    Dim SrcWbk As Workbook
    Dim StrtSht As Worksheet
    Dim DestWbk As Workbook
    
    
    Set StrtSht = ActiveSheet
     Worksheets("GMCR Extract").ListObjects("GMCR_tbl").AutoFilter.ShowAllData
     
     Set SrcWbk = Workbooks.Open(Filename:=StrtSht.Range("U3").Value)
   
   
    With SrcWbk.Sheets("Tbl_M_GMCR_Daily_Positions")
        .Range("A1", .Range("R" & Rows.Count).End(xlUp)).Copy _
            DestWbk.Sheets("GMCR Extract").Range("A2")
    End With
End Sub

The destination worksheet in U17 is already open - it is the original worksheet that holds the macro. I would like the destination to be another sheet of the open workbook.

 

[Fredek]

Object definer error:

Sub Tmp()
    
    Dim SrcWbk As Workbook
    Dim StrtSht As Worksheet
    Dim DestWbk As Workbook
    
    
    Set StrtSht = ActiveSheet
    Set DestWbk = ActiveWorkbook
     Worksheets("GMCR Extract").ListObjects("GMCR_tbl").AutoFilter.ShowAllData
     
     Set SrcWbk = Workbooks.Open(Filename:=StrtSht.Range("U3").Value)
   
   
    With SrcWbk.Sheets("Tbl_M_GMCR_Daily_Positions")
        .Range("A1", .Range("R" & Rows.Count).End(xlUp)).Copy _
            DestWbk.Sheets("GMCR Extract").Range("A2")
    End With
End Sub

 

[Fluff]

Are the files either xlsx or xlsm files?

 

[Fredek]

The source workbook is .xls and the destination is .xlsm

 

[Fluff]

In that case you need to put a . in front of the rows.count

.Range("A1", .Range("R" & .Rows.Count).End(xlUp)).Copy

otherwise rows.count will return 1048576 which is beyond the range of an xls workbook

 

[Fredek]

Works perfectly! Thank you very much for taking the time to help me!

 

[Fluff]

Glad to help & thanks for the feedback