Creating a list of data using a command button

[Cazzdevil]

Hi everyone, really hoping you can help me on this one.

I'm creating a new Time in Motion sheet for my team, and I want to programme a Command Button so that when it's clicked on Sheet 1 it prompts for a reference number to be input, then that reference number is fed into (say) column D on Sheet 2, but then when the button is clicked again it adds the next reference number to the next empty row down in the column gradually generating a list.

The idea is that we have 8 different tasks performed throughout the day multiple times each so I want to create a list of the cases worked on for each task.

I tried searching the forum already and found a thread with this on it:

Dim ans As String
ans = InputBox("Enter a value", "Data Entry")
Range("D" & Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row + 1).Value = ans

... but it's not copying to sheet 2, and it's overwriting the first reference number that was input. I'd be really grateful for an explanation of what's wrong here too so I can learn from it - I'm brand spanking new to excel and have learned loads in the past week so I'm keen to learn more

Thanks so much xx

 

[ozbod]

You need to reference sheet2 as below

Dim ans As String
ans = InputBox("Enter a value", "Data Entry")
[COLOR=#800080][B]Sheets("Sheet2").[/B][/COLOR]Range("D" & Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row + 1).Value = ans

 

[Cazzdevil]

Thanks so much Ozbod, that works a treat.

So I guess I need to specify the sheet first before I set the range.

I think I might delve a bit deeper into the realms of Visual Basic... I had no idea what it was capable of

 

[ozbod]

No problems Cazzdevil,

In your case it was setting the range in the active sheet based on the last row in the second sheet and writing the value.

... and I agree, there is so much you can do in VBA that would take you ages to do manually, and normally in a few lines of code once you understand it better.

Well worth exploring

 

[Cazzdevil]

I see, that makes more sense. The language will be tough to get to grips with, but it's like learning any new language I suppose - once you understand the foundations it's easier to piece the rest together. I'm loving it though, everyone at work is calling me an Excel geek now but it's a moniker I'm proud of.

Don't suppose you could talk me through my next problem could you?
http://www.mrexcel.com/forum/excel-questions/792674-command-button-prompt-input-2-columns-data.html