How to have user to input destination sheet

[LLT]

Dear Expert,

Currently I have hardcoded the destination sheets to be "Cashbook" whereby the data will be copied to. If I need user to input the destination sheet, how do I code the macro?

Dim lr As Long, r As Long
Dim ws As Worksheet
Dim wsName As String
Dim wsDest 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

 

[AlphaFrog]

Try something like this...

With ws
    UsedRange.Copy Destination:=[COLOR="Red"].[/COLOR]Range("A" & Rows.Count).End(xlUp).Offset(1)
End With

Or just this...

    UsedRange.Copy Destination:=[COLOR="Red"]ws.[/COLOR]Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[LLT]

Hi

I would like to explain my code first. "wsName=Sheets("Instruction").Range("H4")" is referring to the sheet in which the data will be copied from.

And then I want it to be copied to "Cashbook" sheet. This is where I have hard coded the destination sheet. But if now if I want to replace the "Cashbook" sheet to allow user to input the name of the sheet that they name. How to go about doing it.

Thanks so much!!


[Dim lr As Long, r As Long
Dim ws As Worksheet
Dim wsName As String
Dim wsDest 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 <!-- / message -->]