Cell reference file path

[Fredek]

Hi guys,

Short question today, in cell A2 I have the file I want the VBA to open:

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! <tbody> </tbody><colgroup><col></colgroup> <tbody> </tbody><colgroup><col></colgroup>

<tbody>
</tbody><colgroup><col></colgroup>

 

[Fluff]

try

Workbooks.Open Filename:=Range("A2").Value

 

[Fredek]

Thank you!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Fredek]

Follow up question!

If I then try to copy information from the recently opened workbook using cell A2. Can I do something like this:

 lastRowSource = Worksheets("Source").Range("A" & Worksheets("Source").Rows.Count).End(xlUp).Row
'to count the number of rows I want to copy
        

Workbooks("Destination.xlsm").Worksheets("Sheet1").Range("A1").Value = [COLOR=#FF0000]Workbooks Filename:=Range("U3").ValueRange("A1","R"& LastRowGMCR).Value[/COLOR]

How do I change the part in red for the macro to use U3 Sheet1 as the destination of the data paste?

 

[Fluff]

3 questions
Is the other workbook open?
What sheet & range of the destination workbook do you want to copy to?
What sheet & range of the workbook just opened do you want to copy from

 

[Fredek]

Hi Fluff,

3 questions
Is the other workbook open? It can be, this was the reason for the initial question! If it is possible to get the information with the workbook closed then even better!
What sheet & range of the destination workbook do you want to copy to? I would like to paste it into A2 of GMCR_tbl named table in the open workbook called Scoring.xlsm.
What sheet & range of the workbook just opened do you want to copy from? - The full path of the source is in cell "U3". Sheet1 Columns A to R to last row. Hence my attempt at "LastRow"

Thank you very much for all your help!

 

[Fluff]

Ok if I understand you correctly
we open a source workbook (U3), copy the usedrows from sheet1 cols A to R
open a destination workbook (A2) & paste the copied data into sheet1 cell A2
If that is correct try

Sub Tmp()
    
    Dim DestWbk As Workbook
    Dim SrcWbk As Workbook
    Dim StrtSht As Worksheet
    
    Set StrtSht = ActiveSheet
    Set DestWbk = Workbooks.Open(FileName:=StrtSht.Range("A2").Value)
    Set SrcWbk = Workbooks.Open(FileName:=StrtSht.Range("U3").Value)
    
    With SrcWbk.Sheets("sheet1")
        .Range("A1", .Range("R" & Rows.Count).End(xlUp)).copy _
            DestWbk.Sheets("Sheet1").Range("A2")
    End With
End Sub

As I don't use tables I'm not to sure if this will work.

 

[Fredek]

Hi Fluff, I've adapted the code but I am getting an object defined error on the copy and paste bit in blue below. Any idea why I'm getting an object definer error?

Sub Tmp()
    
    Dim SrcWbk As Workbook
    Dim StrtSht As Worksheet
    
    

     Worksheets("GMCR Extract").ListObjects("GMCR_tbl").AutoFilter.ShowAllData
     
     Set SrcWbk = Workbooks.Open(Filename:=StrtSht.Range("U3").Value)
   
    
    [COLOR=#0000CD]With SrcWbk.Sheets("Tbl_M_GMCR_Daily_Positions")
        .Range("A1", .Range("R" & Rows.Count).End(xlUp)).Copy _
             Workbooks("Book.xlsm").Worksheets("GMCR Extract").Range("A2")[/COLOR]
    End With
End Sub

 

[Fluff]

I'm surprised that it's even getting that far. Here

     Set SrcWbk = Workbooks.Open(Filename:=[COLOR=#ff0000]StrtSht[/COLOR].Range("U3").Value)

you are using the variable StrtSht, but you have not given it a value.