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.