Autofilter a range in a different instance of excel

[Alan]

Hi All,

This is a follow on problem from my posting earlier, but is a totally
different issue (I think) so I stated a new thread.

Previous thread about exporting range of data to new instance of
excel:



I have now done that successfully (thanks guys).

However, my code also attempts to autofilter the exported range for
the convenience of the user (see below).

I have commented where I think the problem area starts and finishes
(between the XXXX lines).

What the code actually does is remove (or add if already removed) the
autofilter in the source workbook application instance, rather than
the
destination (exported) workbook application instance.

Any suggestions?

Thanks,

Alan.


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+


Private Sub cmdExportDatabase_Click()


Set newxlApp = New Excel.Application

newxlApp.Visible = True

Set newBook = newxlApp.Workbooks.Add

newBookName = ("Export as at " & Format(CDate(Now()), "HHMM DDDD D
MMM YYYY"))

With newBook

.Title = newBookName
.Subject = "Inventory"
.SaveAs Filename:=newBookName

End With


ThisWorkbook.Worksheets("Database").Activate

Cells.Select

Selection.Copy


newBook.Worksheets("Sheet1").Activate

newBook.Worksheets("Sheet1").Range("A1").PasteSpecial
(xlPasteValues)

newBook.Worksheets("Sheet1").Cells.Validation.Delete

' XXXXXXXXXXXXXXXXXXXXXXXXXXXXX

' This is where I think it goes wrong - the autofilter does not get
' applied to the selected range (as I believe I am selecting)


newBook.Worksheets("Sheet1").Activate

newBook.Worksheets("Sheet1").Range("A2").Select

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter

' I tried this too:

' Set ExportedRange = newBook.Worksheets("Sheet1").Range("A2")
' Range(ExportedRange, ExportedRange.End(xlToRight)).Select

' But that does not work either - invalid code apparently

' End of problem?

' XXXXXXXXXXXXXXXXXXXXXXXXXXXXX

newBook.Worksheets("Sheet1").Range("A1").Copy
newBook.Worksheets("Sheet1").Range("A1").Select



ThisWorkbook.Worksheets("Active").Activate


MsgBox ("Database exported")


End Sub


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

 

[Tom Ogilvy]

Dim r1 as Range
newBook.Worksheets("Sheet1").Activate
set r1 = newBook.Worksheets("Sheet1").Range("A2")
set r1 = r1.Parent.Range(r1, r1.End(xlToRight))
set r1 = r1.parent.Range(r1, r1.End(xlDown))
r1.AutoFilter


selection will refer to the selection in the instance of Excel with the
code. You will need to fully qualify references to objects in the other
instance.

 

[Alan]

Dim r1 as Range
newBook.Worksheets("Sheet1").Activate
set r1 = newBook.Worksheets("Sheet1").Range("A2")
set r1 = r1.Parent.Range(r1, r1.End(xlToRight))
set r1 = r1.parent.Range(r1, r1.End(xlDown))
r1.AutoFilter


selection will refer to the selection in the instance of Excel with
the code. You will need to fully qualify references to objects in
the other instance.

Thanks Tom.

I obviously need to upskill on the scope of references when I have
more than one instance open.

Regards,

Alan.

 

[Alan]

Dim r1 as Range
newBook.Worksheets("Sheet1").Activate
set r1 = newBook.Worksheets("Sheet1").Range("A2")
set r1 = r1.Parent.Range(r1, r1.End(xlToRight))
set r1 = r1.parent.Range(r1, r1.End(xlDown))
r1.AutoFilter


selection will refer to the selection in the instance of Excel with
the code. You will need to fully qualify references to objects in
the other instance.

Hi Tom (or anyone else who cares to join the discussion),

This is for my (and others') understanding of the use of the 'parent'
property.

In your above answer, what is the (conceptual?) difference between the
following two statements?

set r1 = r1.Parent.Range(r1, r1.End(xlToRight))

set r1 = newBook.Worksheets("Sheet1").Range(r1, r1.End(xlToRight))


I understood that the parent object (pObj) of a given object (gObj) is
the object that 'contains' our gObj.

If I then understand the excel object model (big assumption!) the
following expressions are equivalent in the example above:

r1.parent
newBook.Worksheets("Sheet1")

since newBook.Worksheets("Sheet1") is the object that 'contains'
newBook.Worksheets("Sheet1").range("A2")


Am I missing something here? Or is it perhaps just a matter of taste?

Thanks in advance,

Alan.

 

[Tom Ogilvy]

Yes, they are equivalent.

You can use whatever gives you the proper object.