ExcelBanter - Autofilter a range in a different instance of excel

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Autofilter a range in a different instance of excel (https://www.excelbanter.com/excel-programming/304377-autofilter-range-different-instance-excel.html)

Autofilter a range in a different instance of excel

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").PasteSpec ial
(xlPasteValues)

newBook.Worksheets("Sheet1").Cells.Validation.Dele te

' 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

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

Autofilter a range in a different instance of excel

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.

--
Regards,
Tom Ogilvy

"Alan" wrote in message
...

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").PasteSpec ial
(xlPasteValues)

newBook.Worksheets("Sheet1").Cells.Validation.Dele te

' 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

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

Autofilter a range in a different instance of excel

"Tom Ogilvy" wrote in message
...

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.

Autofilter a range in a different instance of excel

Yes, they are equivalent.

You can use whatever gives you the proper object.

--
Regards,
Tom Ogilvy

"Alan" wrote in message
...
"Tom Ogilvy" wrote in message
...

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.