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]

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



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.

I am sorry for the confusion. It is not important.

I implemented your solution and it worked. Thank you for that.
I was just trying to learn why it is that the pasting method in your solution does not append a table, while other methods of pasting that i have used in other projects, do append a table.
It is not necessary for me to change the code at all as for the current project, I just resize the table around the new data. No problem.
But just for my understanding, it wondered how they are different, and how, if it is possible, your code solution would be changed so that it did append a table.
I think it is because your code does not 'select'. I am not sure.

 

[DanteAmor]

They are strange things of excel.
It is complicated for vba to work with tables.