How to copy columns of data to another worksheet columns

[ericwangjt]

Hi, every time when user use the userform, data is pasted to few of columns and so far I have managed to paste the values in worksheet 1. Following are the column headers in sheet1.

Date of Entry Item Code Product Description Serial Number Qty

How do I copy the values of Date of Entry, item code, product description and serial number to another work sheet named Serial No?

Secondly, values of the serial number in a combo box depends on the selection of the product selected. The values of the serial number is kept in a worksheet named Serial No. Values of the serial number shown in the userform is coded in a way by referring Serial No worksheet which has multiple rows of records from column A to D.

When product is consumed, the entire row of that product serial number should be deleted in the Serial No worksheet which is column A to D so that user should not be able to select that serial number in the userform as same product type may have different serial number.

May I know how do I achieve that?

[dflak]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[ericwangjt]

Please refer to the attach. I have tried by watching youtube but doesn't works well therefore the copy of range of data to another worksheet is not in there.

Secondly just to ask can I just copy the whole excel and change the worksheet name and the code accordingly and still works? I tried to do that but always get an error when launching the userform. Debug shows MMS_SPARE_IN.show

[dflak]

In answer to your second question, I'd have to look at the code. If the code references specific worksheet names, then the code will break if these sheet names are changed. On the other hand, tables and named dynamic ranges know where they are even when the sheet names on which they are are changed.

[ericwangjt]

Hi, in the code did reference to specific worksheet names. However I did a run through of the code there is no left out/overlook so I not sure where went wrong and do you think is possible because the debug bring me to the MMS_SPARES_IN. Does that mean it could be code problem in the MMS SPARES IN form?

[dflak]

Let me see if I can get the requirement tied down a bit more. I am confused

The form launched from the ISCS Spares In sheet is used to add stock to the inventory. The lookup values for the names come from where? The serial number has to be a free-form entry.

The from launched from the ISCS Spares Out sheet is used to remove stock from inventory. I can see where the lookups can come from the serial number sheet and you want to further "caveat" the lookup by restricting it to serial numbers not already expended.

The first thing I would do is convert the range of data on the Serial No. sheet to an Excel table. Tables know how big they are, and they copy down formulas automatically as data are added to them. Also, they are easier to reference in both formulas and VBA.

I would add a helper column to the Serial Number table to indicate when a serial number is picked for removal. Then make a pivot table of name and serial number with this field as a report filter. Use VBA to set the filter for name and where serial number has not been picked. Overlay the pivot table result with a named dynamic range. Use the range to populate the combo box.

First I would like a clarification as to my two first statements.

[ericwangjt]

Let me see if I can get the requirement tied down a bit more. I am confused

The form launched from the ISCS Spares In sheet is used to add stock to the inventory. The lookup values for the names come from where? The serial number has to be a free-form entry.

The from launched from the ISCS Spares Out sheet is used to remove stock from inventory. I can see where the lookups can come from the serial number sheet and you want to further "caveat" the lookup by restricting it to serial numbers not already expended.

The first thing I would do is convert the range of data on the Serial No. sheet to an Excel table. Tables know how big they are, and they copy down formulas automatically as data are added to them. Also, they are easier to reference in both formulas and VBA.

I would add a helper column to the Serial Number table to indicate when a serial number is picked for removal. Then make a pivot table of name and serial number with this field as a report filter. Use VBA to set the filter for name and where serial number has not been picked. Overlay the pivot table result with a named dynamic range. Use the range to populate the combo box.

First I would like a clarification as to my two first statements.

Answer to the first statement: The lookup values for name is comes from the worksheet "Name" which i create in Excel Formulas>Name Manager. The serial number entry is a free form entry.
Answer to the 2nd statement: ISCS Spares In sheet allows user to make free form entry but only based on the available option given in item description. Free form serial number has to be possible since same products could have different serial number.
When comes to ISCS Spares Out, the form has to populate out all the serial numbers based on the selection of the item e.g product 'A'. Once the spares out is 'added', next user who re-select the same product code e.g product A again, in the serial number combo box shouldn't populate that serial number which has already consumed.
I tried to have a Spares In & Out serial number in same worksheet not thinking how to get it work.
In fact I thinking of have two different spreadsheet because I would like to have one worksheet having all the products serial number that comes in and out(this is for the record when comes to stock take). Another spreadsheet is for the serial number combo box to reference.

[dflak]

On the ISCS Spares IN Form, the Add Button does not seem to work. I assume this is one of the things you would like me to fix.

On the ISCS Spares OUT Form, it looks like what you want to do is add this to the Spares Out Table.

I propose another design. Have both forms write to the same table.

When the Spares IN form is used, it will add the record and fill in the field for Date IN.

When the Spares OUT form is used, it will find the appropriate record based on Serial number, enter in a Date OUT and set a flag (helper column) stating that the serial number has been used.

The serial number drop down box will be populated from this single table, but will not include items whose flags indicate that the item has already been used. It will not be there for selection.

[ericwangjt]

On the ISCS Spares IN Form, the Add Button does not seem to work. I assume this is one of the things you would like me to fix.
Yes.

On the ISCS Spares OUT Form, it looks like what you want to do is add this to the Spares Out Table.

I propose another design. Have both forms write to the same table.

When the Spares IN form is used, it will add the record and fill in the field for Date IN.

When the Spares OUT form is used, it will find the appropriate record based on Serial number, enter in a Date OUT and set a flag (helper column) stating that the serial number has been used.

The serial number drop down box will be populated from this single table, but will not include items whose flags indicate that the item has already been used. It will not be there for selection.
Agreed. But how do i achieve that in the code? I am a vba beginner.

Hi dflak,

Please refer to my reply in blue.

[dflak]

I can help you with that, but it will have to wait until next year .