VBA allow user to choose which Worksheet to target

[GuyGadois]

I have the following simple Macro. The problem is now I want the user to pick which Sheet they want to paste the information on. I have Account_1, Account_2, Account_3, etc. tabs and there may be more the user adds so it is never going to be predetermined. I would like for the user to pick from a list when the Macro starts and it pastes the info in the chosen worksheet, range A4. Is this possible?

Sub MoveData()
Call ShowRows
Selection.Copy
Application.Goto ActiveWorkbook.Sheets("Account 1").Range("A4")
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Call HideRows
End Sub

Cheers,

Guy

 

[Jeffrey Mahoney]

Does this help?

Sub MoveData()
  Dim v As Variant
  Dim GoSht As Worksheet
  Dim GoWB As Workbook
  Dim A As String
  
  On Error Resume Next                    'Have the user point to the right sheet
  Set v = Application.InputBox("Find the sheet where you want to copy the data", "Activate Sheet", Type:=8)
  On Error GoTo 0
  
  If v Is Nothing Then Exit Sub
  A = v.Parent.Parent.Name                    'Save the workbook and worksheet into vars
  Set GoWB = Workbooks(A)
  Set GoSht = Sheets(v.Parent.Name)
  
  Call ShowRows
  Selection.Copy
  GoSht.Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
  Call HideRows
End Sub