Add Data to a Specific Worksheet If Condition is met

[s2jrchoi]

All,

Can you please help me? I have been trying to figure this out for over 4 hours now and I am running low on energy and brain power.

I currently have a working userForm with text fields and two buttons (Add Data / Cancel) to add data to a worksheet. What I would like it to do is add the data in a specific worksheet if a condition is met. For example, in Excel, I have 4 separate worksheets for years 2010, 2011, 2012, and 2013. In my UserForm, I have a field requesting for the specific year. I would like to know what code I can put in where if the user enters 2010, all of the data from the UserForm will be placed in the 2010 worksheet, 2011 data will be placed in the 2011 worksheet, and so forth. Is this something that can be done? How do I go about getting this done?

Any help/insight will be greatly appreciated.
___________________________________________________________

Please Login or Register  to view this content.

[Norie]

Try this.

Please Login or Register  to view this content.

[s2jrchoi]

Thank you for your help Norie but I am getting an error from this line:

Please Login or Register  to view this content.

. Was I supposed to change something from this line? I am a VBA noob, by the way!

Thank you again.

[Norie]

What's the error and what's the value in YearInput when you get it?

PS I've assumed YearInput will hold the year and you have sheets whose names are years.

[s2jrchoi]

This is the error I am receiving: "Run-time Error '438': Object doesn't support this property or method

I do have sheets with names 2010, 2011, 2012, 2013. Was there something I had to do to hold the year in YearInput? If so, is that different from:

Please Login or Register  to view this content.

Thank you again!

[Norie]

Sorry, this is totally my fault - I missed out Worksheets.:oops:

Hopefully this will work.

Please Login or Register  to view this content.

[s2jrchoi]

Thanks again for the quick reply. I am now getting a different error:
Run-time error '9':
Subscript out of range

What can I do to resolve this error?

Thank you!

[Norie]

Do you definitely have sheets called 2010, 2011 etc?

Can you attach your workbook?

[s2jrchoi]

That's weird, it worked after I closed Excel and reopened it. So everything seems to work perfectly. Thank you so much!!!

One last request, if you have time, can you please breakdown what each part of that line means ? I am trying to understand the code that you wrote but I am having a very hard time breaking it down and putting into English..

Please Login or Register  to view this content.

I am so confused...How did your code put the data in the cell I wanted it to? I mean, the table starts on Cell A10 and no where in there does it mention A10..how did it know to put the data there? Also, what does

Please Login or Register  to view this content.

mean?

Thank you again..You are a genius!

[Norie]

This code starts at the bottom row of column A and goes up to find the last row of data, it then offsets by 1 to get the next empty row.

Please Login or Register  to view this content.

[s2jrchoi]

Hi Norie,

Thank you for the information. Quick question, how do you make it so that the data gets entered into the next row from the top and not have it start from the bottom row?

Thank you for your help!!

[Norie]

The code I posted does put the data in the next row from the top.

[s2jrchoi]

It did do that but when I cleaned the slate and started from scratch, the data that I add doesn't go into the table. Can you please see Sheet 2010 from the attached file?

Thank you again for your help!

J