Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to replace advanced auto filter
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to replace advanced auto filter
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to replace advanced auto filter
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to replace advanced auto filter
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to replace advanced auto filter
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to replace advanced auto filter
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to replace advanced auto filter
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to replace Advanced Filter | Excel Discussion (Misc queries) | |||
Find part of a word in Advanced Filter Code | Excel Programming | |||
Problems implementing advanced filter code... | Excel Programming | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Auto filter Code | Excel Programming |