Input Box for Macro

[paulfitz54]

Hi,

I have a macro that retrieves a Job Rating of 5 from a drop-down list and places the results in a new sheet.
What I would like to do is have a message asking me for the job rating (1-5), then run the macro to copy the info to another sheet, will an input box work here? and how would I place it into the code below?

Thanks for your assistance in this
Paul

Copy of the code:

Sub JobRating5()
'
' JobRating5 Macro
' Create new worksheet with only personnel with job rating 5
'

'
Selection.AutoFilter
ActiveSheet.Range("$A$1:$G$742").AutoFilter Field:=7, Criteria1:="5"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("Job Rating").Select
Range("A3").Select
ActiveSheet.Range("$A$1:$G$742").AutoFilter Field:=7
Application.CutCopyMode = False
Range("A1").Select
Selection.AutoFilter
End Sub

 

[Leandroarb]

Hi!

Try:

Selection.AutoFilter
ActiveSheet.Range("$A$1:$G$742").AutoFilter Field:=7, Criteria1:=InputBox("Job Rating here!")
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("Job Rating").Select
Range("A3").Select
ActiveSheet.Range("$A$1:$G$742").AutoFilter Field:=7
Application.CutCopyMode = False
Range("A1").Select
Selection.AutoFilter
End Sub

 

[Fluff]

How about

Sub paulfitz()
   Dim Ans As Variant
   
   Ans = InputBox("Please enter rating")
   If Ans = "" Then Exit Sub
   With Sheets("Job Rating")
      .Range("A1:G1").AutoFilter 7, Ans
      Sheets.Add , Sheets(.Index)
      .AutoFilter.Range.EntireRow.Copy Range("A1")
      .AutoFilterMode = False
   End With
End Sub

 

[Leandroarb]

How about

Sub paulfitz()
   Dim Ans As Variant
  
   Ans = InputBox("Please enter rating")
   If Ans = "" Then Exit Sub
   With Sheets("Job Rating")
      .Range("A1:G1").AutoFilter 7, Ans
      Sheets.Add , Sheets(.Index)
      .AutoFilter.Range.EntireRow.Copy Range("A1")
      .AutoFilterMode = False
   End With
End Sub

Here are two great lessons!

The first is obviously about how to resolve the doubt. The second is as important as the first, which is like summarizing the writing of a code. This saves time and programming lines.

Thank you very much for that great lesson Mr. Pluff!

 

[Leandroarb]

Here are two great lessons!

The first is obviously about how to resolve the doubt. The second is as important as the first, which is like summarizing the writing of a code. This saves time and programming lines.

Thank you very much for that great lesson Mr. Pluff!

The writing was summarized from 552 characters to 303, counting the empty spaces.

 

[paulfitz54]

Hi lads,

Thanks for the quick response, both of your solutions work perfectly

Thanks again
Paul

 

[Fluff]

Glad we could help & thanks for the feedback.