Set source workbook from cell reference

[Davavo]

Hi again, so I have this file selection thing working ok, so the address of the file that i want to use as a data source is in cell "Z2", but I am not sure of the correct syntax for referencing it.
I figured it would be "Indirect" but i get a 'Compile Error' "Sub or function not defined". Actually, i am having a hard time even asking the question in the correct syntax, google is not gifting me appropriate information!

I then want to get this code, written by Fluff, to work on the external file. (I know, I should have specified that to start with but, tbh, i didn't expect so much help, so i started small ... live and learn!)

This code works on sheets in the same workboook. it loops through each sheet and applies an auto-filter and copies the whole row where the filter value is found to a targetsheet in the same workbook.

I want to separate the Targetsheet and the source sheet.

Sub Davavo4()


    
   Dim Ws As Worksheet, Trgtws As Worksheet
   Dim UsdRws As Long
   
   Set Trgtws = Sheets("TargetSheet")
   For Each Ws In Worksheets
      If Ws.Name <> Trgtws.Name Then
      Ws.Columns("k").Hidden = False
         UsdRws = Ws.Range("L" & Rows.Count).End(xlUp).Row
         Ws.Range("A7:L" & UsdRws).AutoFilter 12, "*zhan*"
         Ws.AutoFilter.Range.Offset(1).EntireRow.Copy Trgtws.Range("L" & Rows.Count).End(xlUp).Offset(1, -11)
         Ws.AutoFilterMode = False
      End If
   Next Ws
End Sub

so, i wont need the If statement, as there will be no chance of that conflict. I think what i need to do is define wb as workbook and then set it as the value in the cell Z2, of the active sheet.

Sub Davavo4()



Dim Ws As Worksheet, Trgtws As Worksheet
Dim UsdRws As Long

Set Trgtws = Sheets("TargetSheet")
For Each Ws In Worksheets

Ws.Columns("k").Hidden = False
UsdRws = Ws.Range("L" & Rows.Count).End(xlUp).Row
Ws.Range("A7:L" & UsdRws).AutoFilter 12, "*zhan*"
Ws.AutoFilter.Range.Offset(1).EntireRow.Copy Trgtws.Range("L" & Rows.Count).End(xlUp).Offset(1, -11)
Ws.AutoFilterMode = False

Next Ws
End Sub



Thanks for any help in advance. Learning VBA is Hard.

 

[Davavo]

I think nearly have this. Doesnt actually work mind you! Can anyone help sort out the syntax?

Sub BrowseForFile2()

Dim wb As Workbook
  Dim Ws As Worksheet, Trgtws As Worksheet
   Dim UsdRws As Long


myFile = Application.GetOpenFilename(, , "Browse for Workbook")
ThisWorkbook.Sheets("TargetSheet").Range("z2") = myFile
'open workbook
Workbooks.Open myFile


Set wb = Workbooks.Open(Filename:=myFile, ReadOnly:=False)

With wb

Set Trgtws = Sheets("TargetSheet")
   
   For Each Ws In Worksheets
      
      Ws.Columns("k").Hidden = False
         UsdRws = Ws.Range("L" & Rows.Count).End(xlUp).Row
         Ws.Range("A7:L" & UsdRws).AutoFilter 12, "*zhan*"
         Ws.AutoFilter.Range.Offset(1).EntireRow.Copy Trgtws.Range("L" & Rows.Count).End(xlUp).Offset(1, -11)
         Ws.AutoFilterMode = False
      
   Next Ws

End With

End Sub

no matter where i put

Set Trgtws = Sheets("TargetSheet")

i get subscript out of range error.

Thanks for any help.

 

[DanteAmor]

I think nearly have this. Doesnt actually work mind you! Can anyone help sort out the syntax?

no matter where i put

Set Trgtws = Sheets("[COLOR=#ff0000]TargetSheet[/COLOR]")

i get subscript out of range error.

Thanks for any help.

The error is because you must have a sheet with the name "TargetSheet".
In which book do you have the sheet, in the book with the macro or in the book you are opening?

 

[Davavo]

The error is because you must have a sheet with the name "TargetSheet".
In which book do you have the sheet, in the book with the macro or in the book you are opening?

TargetSheet is the name of a sheet in the workbook with the macro

 

[DanteAmor]

Try this

See the details

Sub BrowseForFile2()
  Dim wb As Workbook, tw As Workbook, Ws As Worksheet, Trgtws As Worksheet, UsdRws As Long, myFile
  
[COLOR=#0000ff]  Set tw = ThisWorkbook[/COLOR]
[COLOR=#0000ff]  Set Trgtws = tw.Sheets("TargetSheet")[/COLOR]
  
  'open workbook
  myFile = Application.GetOpenFilename(, , "Browse for Workbook")
  Trgtws.Range("z2") = myFile
  [COLOR=#0000ff]Set wb [/COLOR]= Workbooks.Open(Filename:=myFile, ReadOnly:=False)
  
  For Each Ws In [COLOR=#0000ff]wb[/COLOR].Worksheets
    Ws.Columns("k").Hidden = False
    UsdRws = Ws.Range("L" & Rows.Count).End(xlUp).Row
    Ws.Range("A7:L" & UsdRws).AutoFilter 12, "*zhan*"
    Ws.AutoFilter.Range.Offset(1).EntireRow.Copy Trgtws.Range("L" & Rows.Count).End(xlUp).Offset(1, -11)
    Ws.AutoFilterMode = False
  Next Ws
  wb.Close False
  MsgBox "End"
End Sub

 

[Davavo]

Try this

See the details

Sub BrowseForFile2()
  Dim wb As Workbook, tw As Workbook, Ws As Worksheet, Trgtws As Worksheet, UsdRws As Long, myFile
  
[COLOR=#0000ff]  Set tw = ThisWorkbook[/COLOR]
[COLOR=#0000ff]  Set Trgtws = tw.Sheets("TargetSheet")[/COLOR]
  
  'open workbook
  myFile = Application.GetOpenFilename(, , "Browse for Workbook")
  Trgtws.Range("z2") = myFile
  [COLOR=#0000ff]Set wb [/COLOR]= Workbooks.Open(Filename:=myFile, ReadOnly:=False)
  
  For Each Ws In [COLOR=#0000ff]wb[/COLOR].Worksheets
    Ws.Columns("k").Hidden = False
    UsdRws = Ws.Range("L" & Rows.Count).End(xlUp).Row
    Ws.Range("A7:L" & UsdRws).AutoFilter 12, "*zhan*"
    Ws.AutoFilter.Range.Offset(1).EntireRow.Copy Trgtws.Range("L" & Rows.Count).End(xlUp).Offset(1, -11)
    Ws.AutoFilterMode = False
  Next Ws
  wb.Close False
  MsgBox "End"
End Sub

Seems to work perfectly!
Thank you so much!

 

[Davavo]

Hi Dante
I notice this method of pasting does not extend the table if there is one there.
The table extends if I type or paste directly below it, but not when this macro puts the data there.
I have also appended a table in a different process, using

Range("A" & lastRow).PasteSpecial Paste:=xlPasteValues

I could always just create a new table or resize, but it seems clumsy.
Can you tell me why this method does not cause the table to automatically extend? And if there is different syntax to make it do so? Or does it involve a totally different method?
Thanks

 

[DanteAmor]

but in the macro there is another method. did you change it?

  Ws.AutoFilter.Range.Offset(1).EntireRow.Copy Trgtws.Range("L" & Rows.Count).End(xlUp).Offset(1, -11)

 

[Davavo]

but in the macro there is another method. did you change it?

  Ws.AutoFilter.Range.Offset(1).EntireRow.Copy Trgtws.Range("L" & Rows.Count).End(xlUp).Offset(1, -11)

No, i didnt change anything.
If Trgtws contains a table A1:L2, this macro will paste the results from A3:L3 and down, but the table does not extend with the data.
If i manually paste data into A3, the table will extend to the new data.

 

[DanteAmor]

Sorry but I am not understanding.
In post #6 you say:

Seems to work perfectly! Thank you so much!

In post #7 you put another code:

Range (" A "& lastRow) .PasteSpecial Paste: = xlPasteValues

The original code has this:

Ws.AutoFilter.Range.Offset (1) .EntireRow.Copy Trgtws.Range (" L "& Rows.Count) .End (xlUp) .Offset (1, -11)

What I changed was to paste from one book to another.


I'm confused.