VBA to Paste data into worksheet specified by user

[JKast]

Hi all,

I want to copy a range of data from one worksheet, and paste it into another worksheet. The worksheet it will be pasted into should be specified by the user. I'm trying to do this by using an inputbox where the user can enter the name of the worksheet he/she would like the data pasted into. This is the code I have so far.

I set sheetName as an integer variable because the names of the worksheets that the data can be pasted into are 1-31. (Representing Days of the month)

I don't think I'm referencing the variable correctly though. I'm new to VBA, I'd appreciate any help.

Sub Data()

Dim sheetName As Integer
Application.ScreenUpdating = False

Range("DataEntry").Copy
sheetName = InputBox("Please enter current day of the month", sheetName)
Worksheets("sheetName").Range("a1:z600").PasteSpecial Paste:=xlPasteValues

End Sub

 

[VoG]

Try

Sub Data()
Dim sheetName As String
sheetName = InputBox("Please enter current day of the month")
Application.ScreenUpdating = False
Range("DataEntry").Copy
Worksheets(sheetName).Range("A1").PasteSpecial Paste:=xlPasteValues
Application.ScreenUpdating = True
End Sub

 

[JKast]

That worked perfectly, thanks a bunch!

 

[mikerickson]

Another approach would be to let the user select the worksheet with the mouse.

Dim uiRange as Range
Dim uiSheet as Worksheet

On Error Resume Next
    Set uiRange = Application.InputBox("Select a sheet with the mouse", type:=8)
On Error Goto 0

If uiRange is Nothing then
    Rem cancel pressed
Else
    Set uiSheet = uiRange.Parent
End If

Hmm...when I get home I need to test

MsgBox TypeName(Application.InputBox("select", type:=10))

when the user clicks a sheet tab and presses OK without clicking on a cell.