Is there code that will replace using advanced autofilter in a shared
workbook? currently when i turn my workbook on to shared, the advanced
filters embedded do not work.

Currently I am using:

Sheets("East Master Repository").Select
Rows("1:1").Select
Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
"Criteria for market groupings").Range("N2:N7"), Unique:=False
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste

Where my filter criteria is in cells N2 thru N7.

try this. When you open a new workbook the focus changes to the new book.

with thisworkbook.Sheets("East Master Repository")
.Rows("1:1").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=thisworkbook.Sheets( _
"Criteria for market groupings").Range("N2:N7"), Unique:=False
set newbk = workbooks.add
.Cells.Copy destination:=newbk.sheets(1).cells

end with

"Belinda7237" wrote:

> Is there code that will replace using advanced autofilter in a shared
> workbook? currently when i turn my workbook on to shared, the advanced
> filters embedded do not work.
>
> Currently I am using:
>
> Sheets("East Master Repository").Select
> Rows("1:1").Select
> Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
> "Criteria for market groupings").Range("N2:N7"), Unique:=False
> Cells.Select
> Selection.Copy
> Workbooks.Add
> ActiveSheet.Paste
>
> Where my filter criteria is in cells N2 thru N7.

I am getting a syntax error i blieve on .Rows

"Joel" wrote:

> try this. When you open a new workbook the focus changes to the new book.
>
> with thisworkbook.Sheets("East Master Repository")
> .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace,
> CriteriaRange:=thisworkbook.Sheets( _
> "Criteria for market groupings").Range("N2:N7"), Unique:=False
> set newbk = workbooks.add
> .Cells.Copy destination:=newbk.sheets(1).cells
>
> end with
>
> "Belinda7237" wrote:
>
> > Is there code that will replace using advanced autofilter in a shared
> > workbook? currently when i turn my workbook on to shared, the advanced
> > filters embedded do not work.
> >
> > Currently I am using:
> >
> > Sheets("East Master Repository").Select
> > Rows("1:1").Select
> > Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
> > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > Cells.Select
> > Selection.Copy
> > Workbooks.Add
> > ActiveSheet.Paste
> >
> > Where my filter criteria is in cells N2 thru N7.

The line was too long and wrapped. I added a continuation character at the
end of the line which was too long.

with thisworkbook.Sheets("East Master Repository")
.Rows("1:1").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=thisworkbook.Sheets( _
"Criteria for market groupings").Range("N2:N7"), Unique:=False
set newbk = workbooks.add
.Cells.Copy destination:=newbk.sheets(1).cells

end with



"Belinda7237" wrote:

> I am getting a syntax error i blieve on .Rows
>
> "Joel" wrote:
>
> > try this. When you open a new workbook the focus changes to the new book.
> >
> > with thisworkbook.Sheets("East Master Repository")
> > .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace,
> > CriteriaRange:=thisworkbook.Sheets( _
> > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > set newbk = workbooks.add
> > .Cells.Copy destination:=newbk.sheets(1).cells
> >
> > end with
> >
> > "Belinda7237" wrote:
> >
> > > Is there code that will replace using advanced autofilter in a shared
> > > workbook? currently when i turn my workbook on to shared, the advanced
> > > filters embedded do not work.
> > >
> > > Currently I am using:
> > >
> > > Sheets("East Master Repository").Select
> > > Rows("1:1").Select
> > > Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
> > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > Cells.Select
> > > Selection.Copy
> > > Workbooks.Add
> > > ActiveSheet.Paste
> > >
> > > Where my filter criteria is in cells N2 thru N7.

Its actually still producing a compile error on the last line indicated its
an invaoid or unqualified reference at .cells?

thoughts?

"Joel" wrote:

> The line was too long and wrapped. I added a continuation character at the
> end of the line which was too long.
>
> with thisworkbook.Sheets("East Master Repository")
> .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace, _
> CriteriaRange:=thisworkbook.Sheets( _
> "Criteria for market groupings").Range("N2:N7"), Unique:=False
> set newbk = workbooks.add
> .Cells.Copy destination:=newbk.sheets(1).cells
>
> end with
>
>
>
> "Belinda7237" wrote:
>
> > I am getting a syntax error i blieve on .Rows
> >
> > "Joel" wrote:
> >
> > > try this. When you open a new workbook the focus changes to the new book.
> > >
> > > with thisworkbook.Sheets("East Master Repository")
> > > .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace,
> > > CriteriaRange:=thisworkbook.Sheets( _
> > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > set newbk = workbooks.add
> > > .Cells.Copy destination:=newbk.sheets(1).cells
> > >
> > > end with
> > >
> > > "Belinda7237" wrote:
> > >
> > > > Is there code that will replace using advanced autofilter in a shared
> > > > workbook? currently when i turn my workbook on to shared, the advanced
> > > > filters embedded do not work.
> > > >
> > > > Currently I am using:
> > > >
> > > > Sheets("East Master Repository").Select
> > > > Rows("1:1").Select
> > > > Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
> > > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > > Cells.Select
> > > > Selection.Copy
> > > > Workbooks.Add
> > > > ActiveSheet.Paste
> > > >
> > > > Where my filter criteria is in cells N2 thru N7.

You could add a column to your table, to check if the item is in your
criteria range. For example, in cell F2:
=COUNTIF($N$2:$N$7,A2)>0

Then, use an autofilter to find the rows that have a TRUE in column F,
and copy the results to a new workbook:

'=============================
Sub ExportMyData()
'copy data and headings from filtered table
'
Dim wb As Workbook
Dim wbNew As Workbook
Dim rng As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set wbNew = Workbooks.Add
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("East Master Repository")
Set ws2 = wbNew.Worksheets(1)

With ws1
If .FilterMode Then
.ShowAllData
End If
.Range("A1").AutoFilter Field:=6, Criteria1:="TRUE"
End With

On Error Resume Next
Set rng = ws1.AutoFilter.Range
On Error GoTo 0
If rng Is Nothing Then
MsgBox "No data to copy"
Else
rng.Copy Destination:=ws2.Range("A1")
End If

End Sub

'=============================

Belinda7237 wrote:
> Is there code that will replace using advanced autofilter in a shared
> workbook? currently when i turn my workbook on to shared, the advanced
> filters embedded do not work.
>
> Currently I am using:
>
> Sheets("East Master Repository").Select
> Rows("1:1").Select
> Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
> "Criteria for market groupings").Range("N2:N7"), Unique:=False
> Cells.Select
> Selection.Copy
> Workbooks.Add
> ActiveSheet.Paste
>
> Where my filter criteria is in cells N2 thru N7.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I don't understand why you are getting this error. Running the new code
below should always work (I removed the filter lines) as long as you have the
worksheet "East MasterRepository". the code creates a new workbook which
should always work. the copy line copies every cell and puts it in the new
workbook in sheet 1. You can't creater a workbook without one worksheet so
this shouldn't fail either. I don't have an answer unless you didn't type
the code correctly.

With ThisWorkbook.Sheets("East Master Repository")

Set newbk = Workbooks.Add
.Cells.Copy Destination:=newbk.Sheets(1).Cells

End With


"Belinda7237" wrote:

> Its actually still producing a compile error on the last line indicated its
> an invaoid or unqualified reference at .cells?
>
> thoughts?
>
> "Joel" wrote:
>
> > The line was too long and wrapped. I added a continuation character at the
> > end of the line which was too long.
> >
> > with thisworkbook.Sheets("East Master Repository")
> > .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace, _
> > CriteriaRange:=thisworkbook.Sheets( _
> > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > set newbk = workbooks.add
> > .Cells.Copy destination:=newbk.sheets(1).cells
> >
> > end with
> >
> >
> >
> > "Belinda7237" wrote:
> >
> > > I am getting a syntax error i blieve on .Rows
> > >
> > > "Joel" wrote:
> > >
> > > > try this. When you open a new workbook the focus changes to the new book.
> > > >
> > > > with thisworkbook.Sheets("East Master Repository")
> > > > .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace,
> > > > CriteriaRange:=thisworkbook.Sheets( _
> > > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > > set newbk = workbooks.add
> > > > .Cells.Copy destination:=newbk.sheets(1).cells
> > > >
> > > > end with
> > > >
> > > > "Belinda7237" wrote:
> > > >
> > > > > Is there code that will replace using advanced autofilter in a shared
> > > > > workbook? currently when i turn my workbook on to shared, the advanced
> > > > > filters embedded do not work.
> > > > >
> > > > > Currently I am using:
> > > > >
> > > > > Sheets("East Master Repository").Select
> > > > > Rows("1:1").Select
> > > > > Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
> > > > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > > > Cells.Select
> > > > > Selection.Copy
> > > > > Workbooks.Add
> > > > > ActiveSheet.Paste
> > > > >
> > > > > Where my filter criteria is in cells N2 thru N7.

Using this, it does create a new workbook, however it errors when it gets to
Advanced filter due to having a shared workbook.

"Joel" wrote:

> I don't understand why you are getting this error. Running the new code
> below should always work (I removed the filter lines) as long as you have the
> worksheet "East MasterRepository". the code creates a new workbook which
> should always work. the copy line copies every cell and puts it in the new
> workbook in sheet 1. You can't creater a workbook without one worksheet so
> this shouldn't fail either. I don't have an answer unless you didn't type
> the code correctly.
>
> With ThisWorkbook.Sheets("East Master Repository")
>
> Set newbk = Workbooks.Add
> .Cells.Copy Destination:=newbk.Sheets(1).Cells
>
> End With
>
>
> "Belinda7237" wrote:
>
> > Its actually still producing a compile error on the last line indicated its
> > an invaoid or unqualified reference at .cells?
> >
> > thoughts?
> >
> > "Joel" wrote:
> >
> > > The line was too long and wrapped. I added a continuation character at the
> > > end of the line which was too long.
> > >
> > > with thisworkbook.Sheets("East Master Repository")
> > > .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace, _
> > > CriteriaRange:=thisworkbook.Sheets( _
> > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > set newbk = workbooks.add
> > > .Cells.Copy destination:=newbk.sheets(1).cells
> > >
> > > end with
> > >
> > >
> > >
> > > "Belinda7237" wrote:
> > >
> > > > I am getting a syntax error i blieve on .Rows
> > > >
> > > > "Joel" wrote:
> > > >
> > > > > try this. When you open a new workbook the focus changes to the new book.
> > > > >
> > > > > with thisworkbook.Sheets("East Master Repository")
> > > > > .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace,
> > > > > CriteriaRange:=thisworkbook.Sheets( _
> > > > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > > > set newbk = workbooks.add
> > > > > .Cells.Copy destination:=newbk.sheets(1).cells
> > > > >
> > > > > end with
> > > > >
> > > > > "Belinda7237" wrote:
> > > > >
> > > > > > Is there code that will replace using advanced autofilter in a shared
> > > > > > workbook? currently when i turn my workbook on to shared, the advanced
> > > > > > filters embedded do not work.
> > > > > >
> > > > > > Currently I am using:
> > > > > >
> > > > > > Sheets("East Master Repository").Select
> > > > > > Rows("1:1").Select
> > > > > > Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
> > > > > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > > > > Cells.Select
> > > > > > Selection.Copy
> > > > > > Workbooks.Add
> > > > > > ActiveSheet.Paste
> > > > > >
> > > > > > Where my filter criteria is in cells N2 thru N7.

I not sure why th e code would fail because of a shared workbook. I suspect
the code is failing because the line is wrapping. I looked again at the way
the code got posted and noticed it look like the Copy line is on two lines
instead of one below I added a line continuation character at the end of the
line to prevent this error

With ThisWorkbook.Sheets("East Master Repository")

Set newbk = Workbooks.Add
.Cells.Copy _
Destination:=newbk.Sheets(1).Cells
End With


"Belinda7237" wrote:

> Using this, it does create a new workbook, however it errors when it gets to
> Advanced filter due to having a shared workbook.
>
> "Joel" wrote:
>
> > I don't understand why you are getting this error. Running the new code
> > below should always work (I removed the filter lines) as long as you have the
> > worksheet "East MasterRepository". the code creates a new workbook which
> > should always work. the copy line copies every cell and puts it in the new
> > workbook in sheet 1. You can't creater a workbook without one worksheet so
> > this shouldn't fail either. I don't have an answer unless you didn't type
> > the code correctly.
> >
> > With ThisWorkbook.Sheets("East Master Repository")
> >
> > Set newbk = Workbooks.Add
> > .Cells.Copy Destination:=newbk.Sheets(1).Cells
> >
> > End With
> >
> >
> > "Belinda7237" wrote:
> >
> > > Its actually still producing a compile error on the last line indicated its
> > > an invaoid or unqualified reference at .cells?
> > >
> > > thoughts?
> > >
> > > "Joel" wrote:
> > >
> > > > The line was too long and wrapped. I added a continuation character at the
> > > > end of the line which was too long.
> > > >
> > > > with thisworkbook.Sheets("East Master Repository")
> > > > .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace, _
> > > > CriteriaRange:=thisworkbook.Sheets( _
> > > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > > set newbk = workbooks.add
> > > > .Cells.Copy destination:=newbk.sheets(1).cells
> > > >
> > > > end with
> > > >
> > > >
> > > >
> > > > "Belinda7237" wrote:
> > > >
> > > > > I am getting a syntax error i blieve on .Rows
> > > > >
> > > > > "Joel" wrote:
> > > > >
> > > > > > try this. When you open a new workbook the focus changes to the new book.
> > > > > >
> > > > > > with thisworkbook.Sheets("East Master Repository")
> > > > > > .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace,
> > > > > > CriteriaRange:=thisworkbook.Sheets( _
> > > > > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > > > > set newbk = workbooks.add
> > > > > > .Cells.Copy destination:=newbk.sheets(1).cells
> > > > > >
> > > > > > end with
> > > > > >
> > > > > > "Belinda7237" wrote:
> > > > > >
> > > > > > > Is there code that will replace using advanced autofilter in a shared
> > > > > > > workbook? currently when i turn my workbook on to shared, the advanced
> > > > > > > filters embedded do not work.
> > > > > > >
> > > > > > > Currently I am using:
> > > > > > >
> > > > > > > Sheets("East Master Repository").Select
> > > > > > > Rows("1:1").Select
> > > > > > > Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
> > > > > > > "Criteria for market groupings").Range("N2:N7"), Unique:=False
> > > > > > > Cells.Select
> > > > > > > Selection.Copy
> > > > > > > Workbooks.Add
> > > > > > > ActiveSheet.Paste
> > > > > > >
> > > > > > > Where my filter criteria is in cells N2 thru N7.

Have you tried your code in a shared workbook? Advanced filter is one of
many features that aren't permitted.

You can manually run an advanced filter on the data, by starting in a
different workbook, but the AdvancedFilter code will create an error.

In my response yesterday, I suggested using an AutoFilter instead.

Joel wrote:
> I not sure why th e code would fail because of a shared workbook. I suspect
> the code is failing because the line is wrapping. I looked again at the way
> the code got posted and noticed it look like the Copy line is on two lines
> instead of one below I added a line continuation character at the end of the
> line to prevent this error
>
> With ThisWorkbook.Sheets("East Master Repository")
>
> Set newbk = Workbooks.Add
> .Cells.Copy _
> Destination:=newbk.Sheets(1).Cells
> End With
>
>
> "Belinda7237" wrote:
>
>
>>Using this, it does create a new workbook, however it errors when it gets to
>>Advanced filter due to having a shared workbook.
>>
>>"Joel" wrote:
>>
>>
>>>I don't understand why you are getting this error. Running the new code
>>>below should always work (I removed the filter lines) as long as you have the
>>>worksheet "East MasterRepository". the code creates a new workbook which
>>>should always work. the copy line copies every cell and puts it in the new
>>>workbook in sheet 1. You can't creater a workbook without one worksheet so
>>>this shouldn't fail either. I don't have an answer unless you didn't type
>>>the code correctly.
>>>
>>>With ThisWorkbook.Sheets("East Master Repository")
>>>
>>> Set newbk = Workbooks.Add
>>> .Cells.Copy Destination:=newbk.Sheets(1).Cells
>>>
>>>End With
>>>
>>>
>>>"Belinda7237" wrote:
>>>
>>>
>>>>Its actually still producing a compile error on the last line indicated its
>>>>an invaoid or unqualified reference at .cells?
>>>>
>>>>thoughts?
>>>>
>>>>"Joel" wrote:
>>>>
>>>>
>>>>>The line was too long and wrapped. I added a continuation character at the
>>>>>end of the line which was too long.
>>>>>
>>>>>with thisworkbook.Sheets("East Master Repository")
>>>>> .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace, _
>>>>> CriteriaRange:=thisworkbook.Sheets( _
>>>>> "Criteria for market groupings").Range("N2:N7"), Unique:=False
>>>>> set newbk = workbooks.add
>>>>> .Cells.Copy destination:=newbk.sheets(1).cells
>>>>>
>>>>>end with
>>>>>
>>>>>
>>>>>
>>>>>"Belinda7237" wrote:
>>>>>
>>>>>
>>>>>>I am getting a syntax error i blieve on .Rows
>>>>>>
>>>>>>"Joel" wrote:
>>>>>>
>>>>>>
>>>>>>>try this. When you open a new workbook the focus changes to the new book.
>>>>>>>
>>>>>>>with thisworkbook.Sheets("East Master Repository")
>>>>>>> .Rows("1:1").AdvancedFilter Action:=xlFilterInPlace,
>>>>>>>CriteriaRange:=thisworkbook.Sheets( _
>>>>>>> "Criteria for market groupings").Range("N2:N7"), Unique:=False
>>>>>>> set newbk = workbooks.add
>>>>>>> .Cells.Copy destination:=newbk.sheets(1).cells
>>>>>>>
>>>>>>>end with
>>>>>>>
>>>>>>>"Belinda7237" wrote:
>>>>>>>
>>>>>>>
>>>>>>>>Is there code that will replace using advanced autofilter in a shared
>>>>>>>>workbook? currently when i turn my workbook on to shared, the advanced
>>>>>>>>filters embedded do not work.
>>>>>>>>
>>>>>>>>Currently I am using:
>>>>>>>>
>>>>>>>>Sheets("East Master Repository").Select
>>>>>>>> Rows("1:1").Select
>>>>>>>> Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
>>>>>>>> "Criteria for market groupings").Range("N2:N7"), Unique:=False
>>>>>>>> Cells.Select
>>>>>>>> Selection.Copy
>>>>>>>> Workbooks.Add
>>>>>>>> ActiveSheet.Paste
>>>>>>>>
>>>>>>>>Where my filter criteria is in cells N2 thru N7.
>>>>>>>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html