Vkackley3300
New Member
- Joined
- Apr 27, 2020
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
Hi. I need a macro to copy data data from another worksheet in the same workbook into the current active worksheet.
I would like to have a dialog box pop up that asks the user to input the sheet name they want to copy from. If they put in an invalid sheet name, it would prompt them that it was an invalid sheet name and to try again or give them the option to cancel. Once they enter a valid sheet name, the macro would need to copy range A1:X76 of that sheet and paste it into the current worksheet.
I found the code below posted in response to another question. It works pretty well except I do not want the data copied into "Copy Data". I would like the data copied into the active worksheet when the macro is initiated. I have tried to modify the code below but I cannot figure it out.
Any help would be appreciated.
Sub copy_data()
Dim response As String, ws As Worksheet
Dim sht As String
100:
response = Application.InputBox("Select a cell in the worksheet to copy from", "Select", Type:=2)
On Error Resume Next
If response = "False" Then Exit Sub
Set ws = Sheets(response)
On Error GoTo 0
If ws Is Nothing Then
MsgBox "Invalid sheet name, enter again or Cancel to exit."
response = ""
GoTo 100
End If
ws.Range("A1:X76").Copy Sheets("Copy Data").Range("A1")
End Sub
I would like to have a dialog box pop up that asks the user to input the sheet name they want to copy from. If they put in an invalid sheet name, it would prompt them that it was an invalid sheet name and to try again or give them the option to cancel. Once they enter a valid sheet name, the macro would need to copy range A1:X76 of that sheet and paste it into the current worksheet.
I found the code below posted in response to another question. It works pretty well except I do not want the data copied into "Copy Data". I would like the data copied into the active worksheet when the macro is initiated. I have tried to modify the code below but I cannot figure it out.
Any help would be appreciated.
Sub copy_data()
Dim response As String, ws As Worksheet
Dim sht As String
100:
response = Application.InputBox("Select a cell in the worksheet to copy from", "Select", Type:=2)
On Error Resume Next
If response = "False" Then Exit Sub
Set ws = Sheets(response)
On Error GoTo 0
If ws Is Nothing Then
MsgBox "Invalid sheet name, enter again or Cancel to exit."
response = ""
GoTo 100
End If
ws.Range("A1:X76").Copy Sheets("Copy Data").Range("A1")
End Sub