How to copy & paste cell values to an excel Table and back?

[inddon]

Hello There,

I have 2 worksheets (Please refer attached Sample Workbook):
1. Data Entry/View/Update:
This is where you enter new data and send to worksheet 'Data Store' to be store in an excel table

2. Data Store:
This is where the above entered data is stored in a table. From here you send the stored data to worksheet 'Data Entry View Update' for view, or modify purpose.

I would like to perform the following 2 tasks using VBA:

1. Send data from 'Data Entry/View/Update' to 'Data Store'

In worksheet 'Data Entry', I enter values of an invoice.
The invoice cell values has it's own Defined Names.

1. When I click the button 'Transfer', it should validate certain values:
a. Check if the 'Invoice Number' (InvNumber) is empty.
If yes then
- display a message 'Invoice Number is empty'
- navigate to 'Invoice Number'
- and exit
else
- Create a row in worksheet 'Data View', in table 'Table1'
- Copy all the cell values from worksheet 'Data Entry' into the last row created in 'Table1'
- Clear the cell values in worksheet 'Data View'
End if

2. Send data from 'Data Store' to 'Data Entry View Update'

When the user clicks on the button 'Data View', it should check t=if the cursor is in a Table Row.

If yes then
- Copy the Table Row data and paste it in worksheet 'Data Entry View Update' in it's respective invoice cell values
else
- display a message 'A table row needs to be selected'
- exit
End if

The mapping of the invoice cell values and the table column names are mentioned in their respective worksheets.



Could you please advice how this can be achieved with the excel table?


Many thanks and look forward to hearing from you.

Regards,
Don

 

[Belleke]

If I understood your question correctly, why not using a userform?
See attached as example, I hope this helps.

 

[inddon]

Wow, amazing, wonderful & excellent Belleke . You did the entire Userform so quick. If there was a choice, I would clicked the 'Like' a 100x

Thank you so much for your sample workbook. For my understanding, I will study your VBA code.

This new approach (UserForm) will bring up a few new additions. I will keep you posted about my progress.

Thanks a lot again.

Regards,
Don

 

[Belleke]

Glad I could help, have a nice day.

 

[inddon]

He

Glad I could help, have a nice day.

Hello Belleke,

In order to understand the working of the sample workbook (Donnie.xlsm) you attached, I am building it piece by piece in a new workbook.

I am in the first sub UserForm_Initialize and I am getting a 'Runtime error 1004 unable to get the max property of the worksheetfunction class' In Donnie.xlsm, it works fine!

Attached workbook. Could you please help?

Thanks & regards,
Don

 

[Belleke]

Hi inddon,
Have a look at my example: formulas -> Manage names, you will see there is a defined name range called IDs, in your example you don't have that, that is why you get the error.

 

[inddon]

Hi inddon,
Have a look at my example: formulas -> Manage names, you will see there is a defined name range called IDs, in your example you don't have that, that is why you get the error.

Yes, now I get it, it is used to derive the max number. Thank you very much!

 

[Belleke]

You're welcome.

 

[inddon]

You're welcome.

In order to get the Column Names in the ListBox, I set the property 'Column Names' to True. It won't display the Column Names from the table (attached snapshot).


In the ListBox, is there a way to include only selected columns with their column header from the excel table? Could you please let me know how can this be achieved?

Thanks & regards,
Don

 

[Belleke]

You have to use the RowSource instead of List.
So delete this line .List = [Data_tbl].Value in UserForm_Initialize and use Rowsource
See:

 

[inddon]

Works perfect. Thank you very much

 

[Belleke]

Welcome