Texts and values from one sheet to another sheet with specific criteria and format.

[chintamani.avinash]

In attached sample file, there are two sheets, one is basic data and another is expected result. Expected result is expected from the table given in Basic Data Sheet.

Whenever there is change in the data in the table in Basic Data Sheet there should be automatic change in the table of Expected Result Sheet. It means it should be automatically or manually (by macro to buttons) get updated.

In the table of Basic Data Sheet column of Batch No. two batches are separated by dot instead of comma and in the column of Total Number of Batches, number of batches in which employee has worked is shown. Table start range is A4 and if require it may vary in future. But if position of the table changes in future then it may be located A4 or below that row. If possible, in case if I change table position in basic data sheet, there should not be any problem in code execution. Also, in case of table in Basic Data Sheet, data entry may extend and reach up to 1000 and plus rows.

In the table given in Expected result sheet, all the text and values in all columns should be automatically or manually get updated when texts or values get change in the Basic Data sheet. Column of Batch No is expected to get arranged from small number to large number in all cells. Batches in numbers will be reflected in In Number column.

Your help is appreciable as I am basic user and expecting (if possible) explanation of the code so that i can also work on it if require.
Thank you.

[maniacb]

Give this code a try. I still need to sort the consolidated batch number field.

Please Login or Register  to view this content.

[chintamani.avinash]

Hello, code extracts data in proper manner. Just following issues found.
1. In the column 'Batch No.', when numbers are in large quantity (as shown in image), width of the column become wider. It will be fine if numbers will be autofit in the same cell by text wrap function. (By removing last line "rs.Range("E7:I" & c).Columns.AutoFit", this problem no 1 is solved).
2. In the column 'In Numbers', batch number of the employee is not reflected. (possibly you are working on it and will get reflected soon.))
3. And I know as you are working on sort function, sorting will be available for department and employee Batch No.

Thanks for your help.

[maniacb]

Here is the updated code. Let us know how it goes.

Please Login or Register  to view this content.

[chintamani.avinash]

Great...
Its working. Brilliant. You smoothen my job. Adding reputation friend.


Just one Issue: When columns like 'Name of the employee', 'Batch No.' and 'Total Number of Batches' remains blanks, code produce error. Instead of error is it possible to show message that 'Fields are Blanks' or something like that.

Thanks for your help.

[maniacb]

I've updated the code to include "Blank Field" whenever a Name is blank, and update the batch number to "0" when that field is blank. You'll see the "0" in the batch number field and it is still counted as an item. The Titles are also bolded.

Please Login or Register  to view this content.

[chintamani.avinash]

Hello,
Following error is occurred. Screenshot is attached.

[maniacb]

ensure the following code is under the updated code:

Please Login or Register  to view this content.

[chintamani.avinash]

Thanks...It is Working greatly.
Just one issue is that, in Basic Data Sheet numbers are stored in Text format but when code executes it converts into General format. This converts 10.20 into 10.2 as shown in image. File is also attached.

[maniacb]

I added one line of code to format that cell as text.

Please Login or Register  to view this content.

I also added a line to autofit the rows.

Please Login or Register  to view this content.

Here is all the code with updates.

Please Login or Register  to view this content.

[chintamani.avinash]

excellent, Code works smooth and giving perfect result. Thanks a lot.

Only one concern from my side, when I tried to collaborate this code into my main file for some extra output. I tried to add extra two columns in "J" and "K" columns in which I will put some figures and formula manually. Also I tried to give heading to the table in the range of A1:I5. But when I tried to update table values from the code, all other heading text and two newly created columns also vanished. Is possible that code will refresh text and values in the table region only i.e. Column E to I and other cells will not be refresh so that new Columns, texts will remain intact and will not be changed.

Sorry to bother you but I did not realized that except table other cells are also refreshing.
Your help is appreciable.

[maniacb]

Replace

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

[chintamani.avinash]

Hello,
When I replaced the code following error occurred. But when i remove

Please Login or Register  to view this content.

then there is no error also do not delete any value or words but no idea if there is any other issue.

[JohnTopley]

Error was because the following had italic quotation marks but now corrected:

wstest.Range("E7:I" & wstest.Cells(Rows.Count, 5).End(xlUp).Row).Clear

[chintamani.avinash]

Excellent friend,
All issue is solved.
You are brilliant, humble and having great patience. Thanks a lot...

[maniacb]

Thanks John for the assist. I wrote post #12 with my phone's keyboard. That keyboard uses the italic quotes. Good to know going forward.

[chintamani.avinash]

Error was because the following had italic quotation marks but now corrected:

wstest.Range("E7:I" & wstest.Cells(Rows.Count, 5).End(xlUp).Row).Clear

Thanks John, Excellent work by you made my job truly very smooth. Thanks friend. Adding reputation.

[JohnTopley]

All the real work was done by maniacb so that's where the honours should go.

[chintamani.avinash]

Actually I am thankful to both of you maniacb and John. I got expertise from two experts. Thanks to both of you.

[maniacb]

You’re welcome. I am glad I could help.

[chintamani.avinash]

Hello, Thanks for previous help. It helped me a lot in the work.
Now including below mention code in my another work but finding problem some problems with the following new changes:

1. In Basic Data sheet, now i have made sheet protected in which only Column D to H and J and K are unlocked, whereas others are locked.
2. I have inserted formula in all cell to calculate number of Batch automatically. It is inserted from I8 to I1500.
3. Now i want to work attached code smoothly with the above two conditions in only for the Range(F8:H1500) I.E. for column F,G, & H and row 8 to row 1500 only. So that in other areas i can write anything or add columns.

I am thankful for previous help.
Expecting help for above conditions so that i can use it in new work.

Please Login or Register  to view this content.

[maniacb]

Try this code. I just added code to unprotect the sheet upfront and then re-protect the sheet before closing the code. Let us know how this works out.

Please Login or Register  to view this content.

[chintamani.avinash]

Hello Thanks for reply,

blank rows where i have kept formula, because of this result sheet showing 0.0.0.0.0 like error in result sheet, sr. no. 13.