Vba criteria multiple sheets

[Klyde73]

Hello,
I have this kode that works in Sheet1 but i need it to filter from the same inputbox criteria in Sheet2 as well.
My kode is:
Sub Filter_4()
x = InputBox("write the first 4 numbers")
x = Val(x & "000")
y = x + 999

Sheets("Sheet1").Range("$C:$C").AutoFilter Field:=1, Criteria1:=">=" & x, Operator:=xlAnd, Criteria2:="<=" & y

End Sub

Hope anyone knows how.
Regards

 

[Klyde73]

Edit:
Actually i have 6 Sheets called Sheet1, Sheet2 etc.
I need it to filter in all these Sheets Range c:c with only one inputbox value.

 

[vcoolio]

Hello Klyde73,

You could try this:-

Sub Filter_4()

        Dim ws As Worksheet
        x = InputBox("write the first 4 numbers")
        x = Val(x & "000")
        y = x + 999

For Each ws In Worksheets
With ws.[A1].CurrentRegion
       .AutoFilter 3, ">=" & x, xlAnd, "<=" & y
       'Do something here
       .AutoFilter
       End With
Next ws

End Sub

I've assumed the data in each sheet starts in column A, row2 with headings in row1.
I also assume that you would want to do something with the filtered data after filtering.

I hope that this helps.

Cheerio,
vcoolio.

 

[vcoolio]

Hello again Klyde73,

Just an afterthought incase you just want to filter each sheet individually and nothing more.

With your code in post #1 , just change Sheets("Sheet1") to ActiveSheet.
The code will then operate on each individual sheet as you require, whereas my code in post #3 will filter all sheets at once.

Cheerio,
vcoolio.

 

[Klyde73]

Hello vcoolio,

Thanks for helping out, but another issue is that i have 2 more Sheets that i have to leave unfiltered.
Sheets 7 & 8.
But you really have helped me getting gloser to the solution.

 

[vcoolio]

Hello Klyde,

With the code in post #3 , change it to:-

Sub Filter_4()

          Dim ws As Worksheet
          x = InputBox("write the first 4 numbers")
          x = Val(x & "000")
          y = x + 999

For Each ws In Worksheets
[COLOR=#ff0000]If ws.Name <> "Sheet7" And ws.Name <> "Sheet8" Then[/COLOR]

With ws.[A1].CurrentRegion
             .AutoFilter 3, ">=" & x, xlAnd, "<=" & y
             'Do something here
              .AutoFilter
              End With
        End If
Next ws

End Sub

This will exclude Sheets 7 and 8 but will filter the rest.

But again, if you make the change as noted in post #4 , you will be able to filter each sheet individually so whether or not you filter sheets 7 and 8 is totally up to you.

I hope that this helps.

Cheerio,
vcoolio.

 

[Klyde73]

Its just perfect now, thank you so much.
Have a great day.

 

[vcoolio]

You're welcome Klyde. I'm glad that that I was able to help.

Cheerio,
vcoolio.