Link particular cell to the Macro

[LLT]

Dear Expert out there,

Need a great help from you. Currently I have created a macro which has a input prompt whereby user need to key in the sheet name as shown below. If I do not want the input prompt and instead want the user to key in the sheet name in a particular excel cell and then link it to my macro, how am I suppose to do it?

Thanks in advance for your help!

[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]

 

[diddi]

try this...

Dim ws As Worksheet
Dim wsName As String

wsName = range("A1") '<<<<  change to the cell you are using to store sheet name

On Error Resume Next
Set ws = Sheets(wsName)

ps i see you tried to use the code []...
to start a code block use the 'code' keyword inside []
and to finish use the '/code' keyword inside []

 

[Marcelo Branco]

Hi,

Using the Worksheet_Change event maybe something like this
(user-entry in A1)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wkExists As Boolean, wk As Worksheet
    
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        wkExists = False
        For Each wk In ThisWorkbook.Sheets
            If UCase(wk.Name) = UCase(Range("A1").Value) Then
                wkExists = True
                Exit For
            End If
        Next wk
        
        If wkExists Then
            MsgBox "blah blah"
            'Call your macro here
        Else
            MsgBox Range("A1").Text & " is not a valid sheet name for this workbook"
        End If
    End If
    
End Sub

HTH

M.

 

[LLT]

Thanks so much, it works!
Currently I have created the macro such that it it will copy the data to a specific sheet. If now I also want user to input the destination sheet into the excel cell. Then how does it works then? Thanks in advance for your help.

Dim lr As Long, r As Long
Dim ws As Worksheet
Dim wsName As String

wsName = Sheets("Instruction").Range("H4")
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

.UsedRange.Copy Destination:=Sheets("Cashbook").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With

End If
End Sub

 

[Marcelo Branco]

Hi,

Some ideas (untested)

Assuming the destination-sheet name is entered in A2, maybe you can inside the If wkExists then

1. check if the sheet exists, exactly as we did previously with A1-entry

2. Call your macro with a parameter (destination-sheet-name) like
Call MyMacro(Range("A2").Value)

Also you have to change your macro to receive the parameter, something like this

Sub MyMacro(wkDest as String)

and use wkDest in

.UsedRange.Copy Destination:=Sheets(wkDest).Range("A" & Rows.Count).End(xlUp).Offset(1)

HTH

M.

 

[LLT]

Hi,

I have tried your idea but it can't work.
Any other suggestion.