PDA

View Full Version : Solved: Advance Filter problem copying filtered range to different workbook?



Simon Lloyd
02-07-2007, 04:27 AM
This arrangement below works if i dont swap between workbooks, but when i do it fails at the CopyToRange line with "Runtime Error 9", which i thought means the worksheet doesnt exist! any ideas why? ActiveSheet.Name = c.Value & " - " & Format(Date, "dd mmm yyyy")
wsNew = ActiveSheet.Name
Set MyBook2 = ThisWorkbook
MyBook1.Activate
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Details").Range("M1:M2"), _
CopyToRange:=MyBook2.Sheets(wsNew).Range("A1"), _
Unique:=FalseRegards,
Simon

moa
02-07-2007, 04:42 AM
Perhaps workbooks(Mybook2).Sheets etc. would work.

moa
02-07-2007, 04:50 AM
Actually that shouldn't matter unless myBook2 is a string variable instead of a workbook.

Simon Lloyd
02-07-2007, 04:53 AM
Thanks for the response Glen but that solution gives "Runtime Error 13 Type Mismatch"

Any other ideas?

Regards,
Simon

ADDITION: The MyBook's are Declared as Workbooks

Bob Phillips
02-07-2007, 05:37 AM
Simon,

When filtering using a criteria, ostensibily you can only copy to the activesheet.

The way to get around this is to activate the target sheet, then set the source range and the criteria range relative to that sheet.


I got very confused in your code as to what range was on what sheet/book, so I would explicitly qualify everything if it were me.

Charlize
02-07-2007, 05:37 AM
Are the workbooks already saved ? I would probably try something like
'both are dimmed as workbook and already exists
'this one is open and is copied from (ThisWorkbook or Activeworkbook ?)
Set mybook1 = ThisWorkbook
'then open already saved workbook
workbooks.open(...)
'now active workbook is another one
'this one is now open and is copied to (ThisWorkbook or Activeworkbook ?)
Set mybook2 = Thisworkbook(...)
'not sure about this one. must we declare a worksheetvariable ?
mybook2.sheets.add (and then the name that you want)
'activate first one
mybook1.activate
Charlize

Simon Lloyd
02-07-2007, 06:52 AM
ws1.Copy''''''''''''''copied a sheet in Active workbook
ActiveSheet.Name = c.Value & " - " & Format(Date, "dd mmm yyyy") ''''''''Rename sheet
wsNew = ActiveSheet.Name '''''''''give variable for new sheet name
Set MyBook2 = ThisWorkbook ''''''''''make thisworkbook so i can call it later
MyBook1.Activate '''''''''''original workbook that sheet was copied from
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Details").Range("M1:M2"), _ '''''''range in original workbook
CopyToRange:=MyBook2.Sheets(wsNew).Range("A1"), _ ''''''range in new unamed workbook referenced as MyBook2
Unique:=False
I have made some comments Bob to show what is going on, i did think it was as simple as just reference the workbooks in the advance filter code!

Regards,
Simon

Bob Phillips
02-07-2007, 07:23 AM
I have added a new workbook object, MyBook3, to point at the newly created workbook, I loaded Rng for my testing so you need to remove this, and I removed the MyBook1.Activate, as I said the target sheet must be active, i.e. MyBook3 (probably best to make that explicit rather than rely on it being active just because it was just created).



ws1.Copy ''''''''''''''copied a sheet in Active workbook
Set MyBook3 = ActiveWorkbook
Set Rng = MyBook1.ActiveSheet.Range("A1:A10")
ActiveSheet.Name = "xx" & " - " & Format(Date, "dd mmm yyyy") ''''''''Rename sheet
wsnew = ActiveSheet.Name '''''''''give variable for new sheet name
Set MyBook2 = ThisWorkbook ''''''''''make thisworkbook so i can call it later
Rng.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=MyBook1.Sheets("Details").Range("M1:M2"), _
CopyToRange:=MyBook3.Sheets(wsnew).Range("A1"), _
Unique:=False

Simon Lloyd
02-07-2007, 12:06 PM
Charlize, thanks for your suggestion but i am manipulating data after a newly created workbook but before saving!

Bob you won't (or probably do!) believe it i already had tried that apart from using ActiveWorkBook instead of ThisWorkBook!

Another silly mistake but of course i didnt understand that Advanced Filter could only be run from the active workbook.

Thanks again friend!
:thumb

Norie
02-07-2007, 12:34 PM
Simon

Why declare as Workbooks?

Also I don't know what you mean saying Advanced Filter can only be run from the active workbook.

The only problem I can see if you were trying to use a closed workbook.

As long as you qualify all the workbook/worksheets/range references it should work fine.:)

Simon Lloyd
02-07-2007, 02:38 PM
Norie, thanks, the workbooks weren't closed, when i tried recording the Advance filter filtering from one workbook and copying to range in another workbook excel even popped up a warning saying it couldn't be done!

I didnt have to declare MyBook(s) as workbooks but just seemed easier however referencing the workbook to copy to caused a problem!
strange but true!

Regards,
Simon

Bob Phillips
02-07-2007, 04:58 PM
Also I don't know what you mean saying Advanced Filter can only be run from the active workbook.

Advanced Filter can only copy a range to the active sheet, so if the target sheet is in a different sheet than the source, you have to activate that sheet (and by default, that workbook), before the copy.

Norie
02-07-2007, 06:02 PM
xld

Are you sure?

Bob Phillips
02-08-2007, 04:03 AM
I am.

What does that workbook prove?

Simon Lloyd
02-08-2007, 05:25 AM
Norie - hope you dont mind a bit of novice intervention but this is how i see your code:

Set wsData = Worksheets("Master (2)")
Set wsCrit = Worksheets.Add

LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row

wsData.Range("A1:A" & LastRow).AdvancedFilter action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True

Set rngCrit = wsCrit.Range("A2")
While rngCrit.Value <> ""
Set wsNew = Worksheets.Add''''new sheet added, not new book
wsData.Range("A1:E" & LastRow).AdvancedFilter '''''works with original worksheet
action:=xlFilterCopy, CriteriaRange:=rngCrit.Offset(-1).Resize(2), CopyToRange:=wsNew.Range("A1"), Unique:=True''''''''copies to new worksheet same workbook
i might be wrong, but it seems that you then save the new worksheet as a workbook after that, my original problem was just using activsheet.copy which opens it as a new workbook then trying to activate the original wbook and copy the filtered range to the new unnamed wbook.

Regards,
Simon

Norie
02-08-2007, 10:22 AM
Simon

I'm not quite sure what you are asking.

Notice my comment regarding referencing.

In xld's code a reference is made to the newly created workbook here.

ws1.Copy
Set MyBook3 = ActiveWorkbook

And to the workbook the code is in here.

Set MyBook2 = ThisWorkbook

These can then be used in subsequent code whenever you need to refer to these workbooks.

xld

The workbook shows that using code you can use Advanced Filter to copy to another worksheet.

Bob Phillips
02-08-2007, 11:14 AM
xld

The workbook shows that using code you can use Advanced Filter to copy to another worksheet.

I am not sure how THAT workbook demonstrates that because it only has the one sheet, but I am not saying that you CANNOT copy to another sheet, only that you cannot copy to any sheet other than the ACTIVE sheet.

Norie
02-08-2007, 11:27 AM
xld

I still don't thing that's quite right.

I realise that the new sheet is the active sheet after being created.

But even if I activate the original sheet like this just after createing the new sheet the code works.

wsData.Activate