User to input the sheet name

[LLT]

Hi expert out there,

Need to seek your advice. Currently I have coding as shown below whereby I have hard coded the sheets("Estmt").

The problem is users maybe using Estmt tab or Citistmt tab for their data. So I want it to prompt user to input the sheet and then it will link to the sheet and macro will do the classify as shown in my coding below.

Thanks in advance for your help!

==========================


Sub ClassificationD()
Dim lr As Long, r As Long
lr = Sheets("Estmt").Cells(Rows.Count, "C").End(xlUp).Row
For r = lr To 1 Step -1
' Classify - REPATRIATION
If Sheets("Estmt").Range("C" & r) Like "*REPAT*" Or Sheets("Estmt").Range("C" & r) Like "*SOUTH SHADY*" Then
Sheets("Estmt").Range("E" & r).Copy Destination:=Sheets("Estmt").Range("F" & r)
End If
If Sheets("Estmt").Range("B" & r) Like "*DEBIT TRFR*" And Sheets("Estmt").Range("C" & r) Like "*INTER-CO SETTLE*" Or Sheets("Estmt").Range("B" & r) Like "*OUT.T.T*" And Sheets("Estmt").Range("C" & r) Like "*INTER-CO SETTLE*" Or Sheets("Estmt").Range("B" & r) Like "*CREDIT REMIT*" And Sheets("Estmt").Range("C" & r) Like "*INTER-CO SETTLE*" Then
Sheets("Estmt").Range("E" & r).Copy Destination:=Sheets("Estmt").Range("F" & r) '-This is for JP
End If

 

[Peter_SSs]

When posting code please use Code Tags - see my signature block for how to do that (and/or click the Quote button at the bottom of my post to see the tags in use).

Reading/debugging unindented code is very difficult and many potential helpers will just pass over your thread.

See if this does what you want.

Sub ClassificationD()
    Dim lr As Long, r As Long
    Dim ws As Worksheet
    Dim wsName As String
    
    wsName = InputBox("Input your sheet name")
    On Error Resume Next
    Set ws = Sheets(wsName)
    On Error GoTo 0
    If ws Is Nothing Then
        MsgBox wsName & " is not a valid sheet name for this workbook"
    Else
        With ws
            lr = .Cells(.Rows.Count, "C").End(xlUp).Row
            For r = lr To 1 Step -1
                ' Classify - REPATRIATION
                If .Range("C" & r) Like "*REPAT*" Or .Range("C" & r) Like "*SOUTH SHADY*" Then
                    .Range("E" & r).Copy Destination:=.Range("F" & r)
                End If
                If .Range("B" & r) Like "*DEBIT TRFR*" And .Range("C" & r) Like "*INTER-CO SETTLE*" _
                        Or .Range("B" & r) Like "*OUT.T.T*" And .Range("C" & r) Like "*INTER-CO SETTLE*" _
                        Or .Range("B" & r) Like "*CREDIT REMIT*" And .Range("C" & r) Like "*INTER-CO SETTLE*" Then
                    .Range("E" & r).Copy Destination:=.Range("F" & r) '-This is for JP
                End If
            Next r
        End With
    End If
End Sub

If you wanted the InputBox to contain the active sheet name as a pre-filled default then that could also be done.

 

[LLT]

Thanks Peter. It works great!