N
NPell
Hello all,
I have a macro, which is designed to take a Data sheet and then filter
on a column. With this Criteria i want it to distribute to the
indiviudal tabs, adding that data to the bottom.
It works fine, as long as there is something for each criteria.
If, however, one of the criteria is blank, it will take all 65536
rows, and paste those blank cells over.
Can i get the macro to not copy the cells if the criteria autofilter
is blank.
Or perhaps even a different way of copying the data, how it looks for
it?
If you can help, thankyou.
** Here is my macro...
Sheets("Data").Select
Selection.AutoFilter Field:=10, Criteria1:="Criteria 1"
Range("A1:I1").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy
lastrow = Sheets("Criteria 1").Cells(Rows.Count,
"A").End(xlUp).Row
Sheets("Criteria 1").Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False
Range("A1").Select
Sheets("Data").Select
Selection.AutoFilter Field:=10, Criteria1:="Criteria 2"
Range("A1:I1").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy
lastrow = Sheets("Criteria 2").Cells(Rows.Count,
"A").End(xlUp).Row
Sheets("Criteria 2").Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False
Range("A1").Select
*** and so on...
I have a macro, which is designed to take a Data sheet and then filter
on a column. With this Criteria i want it to distribute to the
indiviudal tabs, adding that data to the bottom.
It works fine, as long as there is something for each criteria.
If, however, one of the criteria is blank, it will take all 65536
rows, and paste those blank cells over.
Can i get the macro to not copy the cells if the criteria autofilter
is blank.
Or perhaps even a different way of copying the data, how it looks for
it?
If you can help, thankyou.
** Here is my macro...
Sheets("Data").Select
Selection.AutoFilter Field:=10, Criteria1:="Criteria 1"
Range("A1:I1").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy
lastrow = Sheets("Criteria 1").Cells(Rows.Count,
"A").End(xlUp).Row
Sheets("Criteria 1").Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False
Range("A1").Select
Sheets("Data").Select
Selection.AutoFilter Field:=10, Criteria1:="Criteria 2"
Range("A1:I1").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy
lastrow = Sheets("Criteria 2").Cells(Rows.Count,
"A").End(xlUp).Row
Sheets("Criteria 2").Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False
Range("A1").Select
*** and so on...