Userform to copy data from one sheet to another

Hello everyone,

The added workbook has an example.

On Sheet2 I would like to have a botton which opens a form which has to ask for an input.
This input has to be compared with entries on Sheet1 in column A. If they match it should copy the data from Sheet1 to Sheet2.
But not all data, only the data in column C: Input2. If it doesn't match it should give an error message and the option to either try again or cancel.

I hope you can help me with this!
Also, I would like to request the apostrophe notes with the code so it's easier for me to understand what is going on.

Thanks in advance,

Kind regards,

Marco

Hi Marco-Kun
So, based on your sample file, if the user entered Example3 on the UserForm, Sheet2 would have Example3 in Cell A2 and 8 in Cell B2? If true...what happens on the next selection...should Sheet2 be cleared before adding the next selection?

Hi Marco
I've attached a copy of your workbook that includes code to use either a ComboBox or a ListBox (use one or the other...not both). The first thing I did was created a Dynamic Named Range...see this link http://www.ozgrid.com/Excel/DynamicRanges.htm

You can see the Named Range and it's definition by clicking on Formulas --> Name Manager --> Examples and you'll see it's defined as

Please Login or Register  to view this content.

This makes the Named Range dynamic...as you add items to Sheet1, Column A they'll be added to the Named Range.

The code is fully commented...if you have issues, please let me know.

Hi Marco
If I understand your question correctly

if I add the following rule to the code, it will also copy the value in column C from Sheet1?

I'd think you'd want this code

Please Login or Register  to view this content.

Regarding this question

What code has to be changed if the values which has to be copied needs to be pasted in a table on Sheet2?

For example, I have 2 tables on Sheet2, Table1 and Table2. How do I refer to the table in the code for the values to be pasted in?

Tables are a different animal (if you truly are referring to Tables). Tables are NOT a normal worksheet. I have worked with Tables and can help with that. I'm not expert with them and would need to see your actual workbook with a description of what you wish to do.

Hi Marco

I'd need to know much more of what you're trying to do but the attached seems to update your tables as required. The ComboBox procedure updates Table1...the ListBox procedure updates Table2. They're interchangeable...simply change the Table Reference.

Hi Marco

To do this

How do I move the buttons you made

Go into design Mode on the Developer Tab then place the Mouse over the button...with the left mouse button depressed, drag the Button to it's new location. Exit Design Mode.

Hi Marco
Play with the code in the attached. I've added a couple of comment tags in Sheet1. I only modified the code for the ComboBox but the same can be applied to the ListBox code. Let me know of issues.

Hi Marco
You're welcome...glad I could be of help. If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save

Hello John,

Yes, you were a great help. I know how to mark it as [SOLVED] but I found something.

The comments do indeed copy, if it's a comment with text. However, if a comment has a picture, it won't copy.
It will only make an empty comment on Sheet2.

Kind regards,

Marco

Hi Marco

Try this code...I've tested it but not thoroughly

Please Login or Register  to view this content.

Let me know of issues.