Set source workbook from cell reference

Davavo

Board Regular
Joined
Aug 3, 2019
Messages
82
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.

Code:
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.
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I think nearly have this. Doesnt actually work mind you! Can anyone help sort out the syntax?

Code:
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
Code:
Set Trgtws = Sheets("TargetSheet")

i get subscript out of range error.

Thanks for any help.
 
Last edited:
Upvote 0
I think nearly have this. Doesnt actually work mind you! Can anyone help sort out the syntax?

Code:


no matter where i put
Code:
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?
 
Upvote 0
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
 
Upvote 0
Try this

See the details
Code:
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
 
Upvote 0
Try this

See the details
Code:
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!
 
Upvote 0
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
Code:
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
 
Upvote 0
but in the macro there is another method. did you change it?

HTML:
  Ws.AutoFilter.Range.Offset(1).EntireRow.Copy Trgtws.Range("L" & Rows.Count).End(xlUp).Offset(1, -11)
 
Upvote 0
but in the macro there is another method. did you change it?

HTML:
  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.
 
Upvote 0
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:
Code:
Range (" A "& lastRow) .PasteSpecial Paste: = xlPasteValues

The original code has this:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top