****** Advance filter + Colouring using VBA ****** HELP!!!!!

[James8309]

Hi everyone;

I spent about 2 hrs researching how I could do this. If anyone can
help, that would be great!


I have 180 sheets in one excel file all containing same number of rows
and columns = A1:E737
but each cell has different values. Values are always in same format,
number in two decimal place followed by a single alphabet letter then
number in one decimal or single number or none.

e.g. 3.50Q8.3 or 4.81V or 3.12M9


1. How do I apply advance filter and loop to every worksheet using
VBA?
- I have to use criteria range in K1:T2
- Filtered result must be attached in new sheet.
- i.e. if one sheet is called "50" then filtered result must be
attached to a new worksheet "50A"


2. if filtered data has column 'A' containing 1.40M9, 1.20M8, 8.02M1
etc.. from A1:A737
How do I apply "sort descending order" on last digit and alphabet
excluding first two decimal digits?
i.e. sorting only M0 to M9 in descending order.


2a) How do I fill the colour in from coloum A1:A737 using VBA?


3. Is it possible to program VBA in such way to look up a certain
value "X" in column "A", and if that value was found then fill the
entire row with colour?




is it possible to use VBA to do all of those above?

I ve been studying VBA for 2 months but my knowledge is nowhere close
to answer those questions. Can anyone help please? My brain is going
to explode.

thank you so much!

 

[Joel]

See Comments below

1. How do I apply advance filter and loop to every worksheet using
VBA?
- I have to use criteria range in K1:T2
- Filtered result must be attached in new sheet.
- i.e. if one sheet is called "50" then filtered result must be
attached to a new worksheet "50A"

Do you want to copy the filtered results to a new worksheet or do you want
to filter each sheet that already exists. I can apply a filter to a
worksheet (specify name please) and the go through each item in the filter a
copy to a new page. More details are required.



2. if filtered data has column 'A' containing 1.40M9, 1.20M8, 8.02M1
etc.. from A1:A737
How do I apply "sort descending order" on last digit and alphabet
excluding first two decimal digits?
i.e. sorting only M0 to M9 in descending order.

the best way to do this is to add three auxilary columns and sort on the
auxilary columns

aux 1 - copy formula down entire column
=left(A1,4) 'first four digits contain the number

aux 2 - copy formula down entire column
=Mid(A1,5,1) 'Letter

aux 1 - copy formula down entire column
=right(A1,1) 'Last Number

2a) How do I fill the colour in from coloum A1:A737 using VBA?

Range("A1:A737").interior.colorindex = 3

3. Is it possible to program VBA in such way to look up a certain
value "X" in column "A", and if that value was found then fill the
entire row with colour?

With ActiveSheet
RowCount = 1
Do While .Range("A" & RowCount) <> ""
If .Range("A" & RowCount) = "Auto" Then
Rows(RowCount).Interior.ColorIndex = 3
End If
Loop
End With

 

[James8309]

1) Do you want to copy the filtered results to a new worksheet or do
you want
to filter each sheet that already exists. I can apply a filter to a
worksheet (specify name please) and the go through each item in the
filter a
copy to a new page. More details are required.


=> There are 180 worksheets in one excel file. I want to apply the
same advanced filter criteria to all worksheets.

My worksheet names are in numbers like this;

- 41, 42, 43, 44, 45, 46, 47, 48....etc

Is it possible to put filtered result in new worksheet just like this
- 41, 41R, 42, 42R, 43, 43R, 44, 44R.... etc where worksheet with R
at the end represents filtered results.
worksheet 41R = filtered results from worksheet 41.



2a) if I don't know how many cells are containing data on column A and
if I wanted to fill colours on cells containing data only on column A
How do I do this using VBA? Filtered result might have 100 columns
might have 50 columns of data.




Thank you so much for your help. You are a champ.

 

[Joel]

1) Do you want to copy the filtered results to a new worksheet or do
you want
to filter each sheet that already exists. I can apply a filter to a
worksheet (specify name please) and the go through each item in the
filter a
copy to a new page. More details are required.


=> There are 180 worksheets in one excel file. I want to apply the
same advanced filter criteria to all worksheets.

My worksheet names are in numbers like this;

- 41, 42, 43, 44, 45, 46, 47, 48....etc

Is it possible to put filtered result in new worksheet just like this
- 41, 41R, 42, 42R, 43, 43R, 44, 44R.... etc where worksheet with R
at the end represents filtered results.
worksheet 41R = filtered results from worksheet 41.

For Each sht In ThisWorkbook.Sheets
'make sure sheet isn't a filtered sheet
If Right(sht.Name, 1) <> "R" Then
Set newsht = Sheets.Add(after:=sht)
newsht.Name = sht.Name & "R"
sht.Cells.AutoFilter field:=2, Criteria1:="Auto", _
Operator:=xlOr, Criteria2:="Important"
sht.Cells.SpecialCells(Type:=xlCellTypeVisible).Copy _
Destination:=newsht.Cells

End If
Next sht

2a) if I don't know how many cells are containing data on column A and
if I wanted to fill colours on cells containing data only on column A
How do I do this using VBA? Filtered result might have 100 columns
might have 50 columns of data.

You may not want to use filters
for each cell in Columns("A:A")
if cell <> "" then
cell.interior.colorindex = 3
end if

next cell