Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Need user input to dictate code

I have some code that I found that works perfectly for what I want, which is for an entire sheet to be copied from one workbook to another, but I was hoping to customize it and make it a little bit easier on me (so I don't have to re-code this for all 30 sheets) by allowing the user to specify exactly which sheet they are going to copy.

            Sub Start()
            Dim x As Workbook
            Dim y As Workbook

            '## Open both workbooks first:
            Set x = Workbooks.Open("data workbook")
            Set y = Workbooks.Open("destination workbook")

            'This is where I would like the user to input the destination sheet in x:
            x.Sheets("USER INPUT").Range("A1:z28").Copy

            'Then paste data from x to y:
            y.Sheets("STATIC SHEET").Range("A1").PasteSpecial

            'Close x:
            x.Close
            End Sub

What I want is for a popup box to appear when the Macro is run that will allow the user to input the name of a sheet (located in "data workbook") to copy the information from, and will automatically enter this input into the Macro when accessing the data to be copied.

like image 239
Reamithey Avatar asked Apr 02 '26 13:04

Reamithey


1 Answers

This has @TyMarc 's answer incorporated into the code you posted but it also has an error handler so that if the user input is not the correct name it will give an error message and ask again.

Sub Start()
    On Error GoTo ErrorHandler
    Dim x, y As Workbook
    Dim inp as String

    '## Open both workbooks first:
    Set x = Workbooks.Open("data workbook")
    Set y = Workbooks.Open("destination workbook")

Label1:

    inp = InputBox("Enter the name of the sheet to be copied")

    'This is where I would like the user to input the destination sheet in x:
    x.Sheets(inp).Range("A1:z28").Copy

    'Then paste data from x to y:
    y.Sheets("STATIC SHEET").Range("A1").PasteSpecial

    'Close x:
    x.Close
    Exit Sub 'This is a crutial part otherwise it will finish the program and continue right into the error handler which will send it back to Label1 and start an infinite loop of death...
ErrorHandler:
    MsgBox("The input entered was not the name of a worksheet")
    Resume Label1:
End Sub
like image 99
Tim.DeVries Avatar answered Apr 04 '26 05:04

Tim.DeVries



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!