VBA: "With ... End With" questions

[Frank M]

When using a WITH statement in a macro can I reference a Sheet in another Workbook?
Tried it but couldn't get it to work.

Also, when coding the WITH statement why must I use Sheet1, Sheet2, ... as a reference instead of a Sheet's actual name? Tried it but couldn't get it to work.

Example Code (works fine as coded):

With Sheet1
On Error Resume Next
.AutoFilterMode = False
If Range("A2") = "all" Then
.Range("A8:Q65").AutoFilter Field:=1, Criteria1:="*"
Else
.Range("A8:Q65").AutoFilter Field:=1, Criteria1:=Sheet2.[A2] & "*"
End If
.Range("A8:Q" & .Cells(Rows.Count, "A").End(xlUp).Row).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
Sheet2.[A8].PasteSpecial xlValues
.ShowAllData
End With

 

[NARAYANK991]

Hi ,

I am not able to visualize what the problem can be as far as referencing another workbook in a With statement is concerned.

The answer to your second question is that it is certainly possible to use a tab name in a With statement , as in :

With Worksheets("Sheet1")

If you can upload a sample workbook with some data and your complete code in it , a more precise answer can be given.

Narayan

 

[Frank M]

I have included the source\data\macro in the uploaded Excel file.

Product_List - data file (referenced as Sheet1 in macro).
Sheet2 - listing produced by executing macro.
Macro - Sheet2.Extract

Thanks for your interest.

Frank

 

[NARAYANK991]

Hi Frank ,

See now.

Narayan

 

[Frank M]

Thanks again for you help.

Frank