Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
activate macro | Excel Discussion (Misc queries) | |||
Activate one Book then another Book | Excel Programming | |||
Button to activate a macro | Excel Programming | |||
Worksheet Activate event if only one sheet in book | Excel Programming | |||
search all sheets in book to find value and activate sheet with va | Excel Programming |