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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With