VBA question - using input message box to select a worksheet

LB 2022 21 Reputation points
2022-11-02T03:08:26.523+00:00

I'm new to VBA macros so I'm using code from internet threads and "record macros" to build what I'm looking for

I'm trying to use an input message box to select a range on a sheet and then copy this to another location. The below code worked

**Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

'PickMultiple One
Dim shname As String
Do Until WorksheetExists(shname)
shname = InputBox("Enter sheet name")
If Not WorksheetExists(shname) Then MsgBox shname & " doesn't exist!", vbExclamation
Loop
Sheets(shname).Select**

I then wanted to use the same code to run more than once (I wanted to select from more than 1 sheet) so just copied it twice but it doesn't give me a 2nd message input box. It just uses the same sheet I selected originally and doubles it. Below is what I'm using.

**Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

'PickMultiple One
Dim shname As String
Do Until WorksheetExists(shname)
shname = InputBox("Enter sheet name")
If Not WorksheetExists(shname) Then MsgBox shname & " doesn't exist!", vbExclamation
Loop
Sheets(shname).Select
' RecordandPaste OneMultiple Macro
'
'
Selection.Copy
Sheets("Worksheet").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'PickMultiple Two
Do Until WorksheetExists(shname)
shname = InputBox("Enter sheet name")
If Not WorksheetExists(shname) Then MsgBox shname & " doesn't exist!", vbExclamation
Loop
Sheets(shname).Select

' RecordandPaste Two Multiple Macro
'
'
Selection.Copy
Sheets("Worksheet").Select
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False**

Could someone assist with correcting the cods so after the 1st message box copies and pastes the data a 2nd message box appears to copy from a different sheet

Thanks

0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114K Reputation points
    2022-11-02T11:55:58.073+00:00

    Try executing shname = "" before each Do Until WorksheetExists(shname).


0 additional answers

Sort by: Most helpful