filter - copy - paste

[verluc]

I have the following macro to do a filtering on column L and then the results of this filtering paste in another workbook.Here is my macro but it doesn't work.Why?

Sub Filter()
currentworkbook = ActiveWorkbook.Name
currentsheet = ActiveSheet.Name
Columns("L:L").Select
Selection.autofilter Field:=1, Criteria1:="1"
Range("L1:" & Range("L65536").End(xlUp).Address).Copy
Workbooks("Map1.xls").Sheets("Blad1").Range("A1").PasteSpecial
Workbooks(currentworkbook).Sheets(currentsheet).Activate
Selection.autofilter
End Sub

Nobody can find the error in this macro?
Many thanks for help.

[ This Message was edited by: verluc on 2002-04-17 07:29 ]

[verluc]

On 2002-04-17 03:18, verluc wrote:
I have the following macro to do a filtering on column L and then the results of this filtering paste in another workbook.Here is my macro but it doesn't work.Why?

Sub Filter()
currentworkbook = ActiveWorkbook.Name
currentsheet = ActiveSheet.Name
Columns("L:L").Select
Selection.autofilter Field:=1, Criteria1:="1"
Range("L1:" & Range("L65536").End(xlUp).Address).Copy
Workbooks("Map1.xls").Sheets("Blad1").Range("A1").PasteSpecial
Workbooks(currentworkbook).Sheets(currentsheet).Activate
Selection.autofilter
End Sub

Nobody can find the error in this macro?
Many thanks for help.

[ This Message was edited by: verluc on 2002-04-17 07:29 ]

[verluc]

On 2002-04-17 07:34, verluc wrote:

[Tommy Bak]

Hi Verluc
What seems to the problem with your macro??
Where does it stop or what does it not do right?

I just ran it and here it copied nicely.
regards Tommy

[verluc]

On 2002-04-17 12:54, Tommy Bak wrote:
Hi Verluc
What seems to the problem with your macro??
Where does it stop or what does it not do right?

I just ran it and here it copied nicely.
regards Tommy

I receive an error message on the line:
Workbooks("Map1.xls").Sheets("Blad1").Range("A1").PasteSpecial

Thanks for your time

[Tommy Bak]

I have named a workbook & sheet exactly the same as yours, and your code is OK on my computer.
Your workbook (Map1) has to be open of course for this macro to function.
Check the workbook and sheetname again.
regards Tommy

[Tommy Bak]

By the way. I'm using xl2000.
You could have a problem if you are using xl97.
Then try
Workbooks("Map1.xls").Sheets("Blad1").select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

[verluc]

On 2002-04-17 13:33, Tommy Bak wrote:

I have named a workbook & sheet exactly the same as yours, and your code is OK on my computer.
Your workbook (Map1) has to be open of course for this macro to function.
Check the workbook and sheetname again.
regards Tommy

Tommy,

Sorry,but my macro doesn't work.
For good understanding, I the filtering is ended then the hole lines must be copied and pasted in Map1,Sheets Blad
Thanks for answer.

[Tommy Bak]

Well, it's a strange thing that it work here by me. If you are trying to copy this into a new workbook then try this. It will make a new book and copy the value to that one.


Sub Filter()
currentworkbook = ActiveWorkbook.Name
currentsheet = ActiveSheet.Name
Columns("L:L").Select
Selection.AutoFilter Field:=1, Criteria1:="1"
Range("L1:" & Range("L65536").End(xlUp).Address).Copy
Workbooks.Add
Range("A1").PasteSpecial
Workbooks(currentworkbook).Sheets(currentsheet).Activate
Selection.AutoFilter
End Sub

[verluc]

On 2002-04-17 13:53, Tommy Bak wrote:
Well, it's a strange thing that it work here by me. If you are trying to copy this into a new workbook then try this. It will make a new book and copy the value to that one.


Sub Filter()
currentworkbook = ActiveWorkbook.Name
currentsheet = ActiveSheet.Name
Columns("L:L").Select
Selection.AutoFilter Field:=1, Criteria1:="1"
Range("L1:" & Range("L65536").End(xlUp).Address).Copy
Workbooks.Add
Range("A1").PasteSpecial
Workbooks(currentworkbook).Sheets(currentsheet).Activate
Selection.AutoFilter
End Sub

Great Tom,now it works
Thanks,thanks,thanks