Msg Box to input data

[Jacko058]

I need help on a Macro where the user inputs data into a pop up box.

That data is then copied into another sheet.

 

[mumps]

This macro will place your data in Range("A1") of Sheet2.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Please enter your data.")
    Sheets("Sheet2").Range("A1") = response
    Application.ScreenUpdating = True
End Sub

 

[Jacko058]

This macro will place your data in Range("A1") of Sheet2.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Please enter your data.")
    Sheets("Sheet2").Range("A1") = response
    Application.ScreenUpdating = True
End Sub

Perfect thanks!

Can this be altered to use the .End(xlUp)

so I can input the data into the last selected cell?

 

[mumps]

I'm not sure if I understood correctly, but this will place the data in the first blank row in Sheet2.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Please enter your data.")
    Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = response
    Application.ScreenUpdating = True
End Sub

 

[Jacko058]

I'm not sure if I understood correctly, but this will place the data in the first blank row in Sheet2.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Please enter your data.")
    Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = response
    Application.ScreenUpdating = True
End Sub

Works exactly as I need.

Final thing I need to auto input Date into column D, time into column E and PC username into column F...

 

[Fluff]

Untested but try

Sub CopyData()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Please enter your data.")
    With Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
        .Offset(1, 0) = response
        .Offset(1, 4).Value = Date
        .Offset(1, 5).Value = Time
        .Offset(1, 6).Value = Environ(UserName)
    End With
    Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Untested but try

Sub CopyData()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Please enter your data.")
    With Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
        .Offset(1, 0) = response
        .Offset(1, 4).Value = Date
        .Offset(1, 5).Value = Time
        .Offset(1, 6).Value = Environ([B][COLOR="#FF0000"][SIZE=3]"[/SIZE][/COLOR][/B]UserName[B][COLOR="#FF0000"][SIZE=3]"[/SIZE][/COLOR][/B])
    End With
    Application.ScreenUpdating = True
End Sub

You forgot the quote marks around "UserName" in the Environ function call. Also, for such a small amount of data being written to the worksheet, I think you can dispense with toggling ScreenUpdating. Finally, this is more a personal preference than a comment about your code, you can collapse the last three Offset calls into a single line of code. Here is the code implementing all of the above comments...

Sub CopyData()
  Dim Response As String
  Response = InputBox("Please enter your data.")
  With Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
    .Offset(1, 0) = Response
    .Offset(1, 4).Resize(, 3) = Array(Date, Time, Environ("UserName"))
  End With
End Sub

 

[Fluff]

You forgot the quote marks around "UserName" in the Environ function call

Cheers for that Rick.

Also, for such a small amount of data being written to the worksheet, I think you can dispense with toggling ScreenUpdating

Agreed, I felt it was easier to just modify the code already supplied.

As for the last point, I can't quite decide if I don't like arrays, or if they don't like me!