Activate macro in another book
 

Hi,

This is very much a work in progress and I'm sure my level of expertise will
be readily apparent.

Two problems occur. First when I copy to the 2nd workbook, the column width
is different (hence the "Autofit"). Secondly when I do my first group of
filters in book 2, it shows the correct rows, but hides all other rows down
to 65,450. I could live with that, but I want to do further sets of filters
and I run out of rows.

Any help gratefully received.

Steve W

Sub CopyTodays()

'This Sets Autofilter to select todays entries only from book 1, then copies
to book 2,then filters that data(3 different ways)and copies to another
sheet in book 2


Sheets("All").Select
Selection.AutoFilter field:=1, Criteria1:=Date
Cells.Select
Selection.Copy
Workbooks.Open "C:\Documents and Settings\Steve\My
Documents\Folder1\Folder2\Book2.xls"
Sheets("All").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.EntireColumn.AutoFit
Cells.Select
Selection.AutoFilter field:=6, Criteria1:="=40", Operator:=xlAnd
Selection.AutoFilter field:=9, Criteria1:="<14", Operator:=xlAnd
Selection.AutoFilter field:=15, Criteria1:="1"
Selection.Copy
Sheets("Test list").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("All").Select
-More Filters.......
End Sub

Activate macro in another book
 

Hi Steve

You can use Paste special to also copy the column width
See the example here
http://www.rondebruin.nl/copy5.htm

I not understand the second question

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steve Wallis" wrote in message ...
Hi,

This is very much a work in progress and I'm sure my level of expertise will
be readily apparent.

Two problems occur. First when I copy to the 2nd workbook, the column width
is different (hence the "Autofit"). Secondly when I do my first group of
filters in book 2, it shows the correct rows, but hides all other rows down
to 65,450. I could live with that, but I want to do further sets of filters
and I run out of rows.

Any help gratefully received.

Steve W

Sub CopyTodays()

'This Sets Autofilter to select todays entries only from book 1, then copies
to book 2,then filters that data(3 different ways)and copies to another
sheet in book 2


Sheets("All").Select
Selection.AutoFilter field:=1, Criteria1:=Date
Cells.Select
Selection.Copy
Workbooks.Open "C:\Documents and Settings\Steve\My
Documents\Folder1\Folder2\Book2.xls"
Sheets("All").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.EntireColumn.AutoFit
Cells.Select
Selection.AutoFilter field:=6, Criteria1:="=40", Operator:=xlAnd
Selection.AutoFilter field:=9, Criteria1:="<14", Operator:=xlAnd
Selection.AutoFilter field:=15, Criteria1:="1"
Selection.Copy
Sheets("Test list").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("All").Select
-More Filters.......
End Sub

Activate macro in another book
 

Thanks for the answer Ron

In regard to the second Question, I may copy, say, 30 rows from book 1 to
book 2, then apply the filter in book 2. Normally you would see rows that I
filtered by, with other rows hidden, then see rows from 31 on visible. In
this case, it filters the data rows correctly, but hides rows 31 - 65,449.
65,450 is the next visible row after the filtered data.

Hope this clarifies. I will appreciate any help.

Steve


"Ron de Bruin" wrote in message
...
Hi Steve

You can use Paste special to also copy the column width
See the example here
http://www.rondebruin.nl/copy5.htm

I not understand the second question

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steve Wallis" wrote in message
...
Hi,

This is very much a work in progress and I'm sure my level of expertise
will
be readily apparent.

Two problems occur. First when I copy to the 2nd workbook, the column
width
is different (hence the "Autofit"). Secondly when I do my first group of
filters in book 2, it shows the correct rows, but hides all other rows
down
to 65,450. I could live with that, but I want to do further sets of
filters
and I run out of rows.

Any help gratefully received.

Steve W

Sub CopyTodays()

'This Sets Autofilter to select todays entries only from book 1, then
copies
to book 2,then filters that data(3 different ways)and copies to another
sheet in book 2


Sheets("All").Select
Selection.AutoFilter field:=1, Criteria1:=Date
Cells.Select
Selection.Copy
Workbooks.Open "C:\Documents and Settings\Steve\My
Documents\Folder1\Folder2\Book2.xls"
Sheets("All").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.EntireColumn.AutoFit
Cells.Select
Selection.AutoFilter field:=6, Criteria1:="=40", Operator:=xlAnd
Selection.AutoFilter field:=9, Criteria1:="<14", Operator:=xlAnd
Selection.AutoFilter field:=15, Criteria1:="1"
Selection.Copy
Sheets("Test list").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("All").Select
-More Filters.......
End Sub