Hello all,
I am working on my first macro in Excel 2007 on Window XP Pro. The purpose of the macro is to data based on auto filters from multiple tabs and paste the copied data to a new workbook. The original workbook has 12 columns and the new one only needs data from 2 of these. The macro I built works great unless there is no data left after the filters are applied then it copies all of the data on the spreadsheet that was filtered and hidden. Here is a piece of the macro that I am working on.
Columns("A:L").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$L$900").AutoFilter Field:=9, Criteria1:="="
ActiveSheet.Range("$A$1:$L$900").AutoFilter Field:=4, Criteria1:="<>"
ActiveSheet.Range("$A$1:$L$900").AutoFilter Field:=8, Criteria1:="<>"
Columns("A:B").Select
Selection.EntireColumn.Hidden = True
Columns("E:L").Select
Selection.EntireColumn.Hidden = True
Rows("1:1").Select
Selection.EntireRow.Hidden = True
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").End(xlDown).Offset(1, 0).Select
Windows("AutoRemitTesting.xls").Activate
Sheets("Medicare").Select
The code repeats for each tab on the workbook then saves the new workbook and removes the filters and unhides everything.
I have built it thus far by trial and error and alot of help from reading forums but I can't find anything that helps me solve this issue. Any help would be greatly appreciated.
Thanks!
I am working on my first macro in Excel 2007 on Window XP Pro. The purpose of the macro is to data based on auto filters from multiple tabs and paste the copied data to a new workbook. The original workbook has 12 columns and the new one only needs data from 2 of these. The macro I built works great unless there is no data left after the filters are applied then it copies all of the data on the spreadsheet that was filtered and hidden. Here is a piece of the macro that I am working on.
Columns("A:L").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$L$900").AutoFilter Field:=9, Criteria1:="="
ActiveSheet.Range("$A$1:$L$900").AutoFilter Field:=4, Criteria1:="<>"
ActiveSheet.Range("$A$1:$L$900").AutoFilter Field:=8, Criteria1:="<>"
Columns("A:B").Select
Selection.EntireColumn.Hidden = True
Columns("E:L").Select
Selection.EntireColumn.Hidden = True
Rows("1:1").Select
Selection.EntireRow.Hidden = True
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").End(xlDown).Offset(1, 0).Select
Windows("AutoRemitTesting.xls").Activate
Sheets("Medicare").Select
The code repeats for each tab on the workbook then saves the new workbook and removes the filters and unhides everything.
I have built it thus far by trial and error and alot of help from reading forums but I can't find anything that helps me solve this issue. Any help would be greatly appreciated.
Thanks!