VBA: Copying values from one worksheet to another

[inddon]

Hello There,

I have 2 worksheets:
1. Setup: The combination of 'Row', 'Column' co-ordinates and it's values and 'Group Number' are given. These values need to be copied to the co-ordinates of row-column in and Group Number the worksheet 'Transfer'
2. Transfer


I have attached the sample workbook for your reference and have explained the best I could.

This can be done referencing, but it needs to be done only via VBA.


Could you please advise and help

Thanks & regards
Don

 

[Nebu]

Guess this is what you are looking for...

 

[Nebu]

Here is the file

 

[inddon]

Here is the file

Hello Nebu,

Thank you for your solution. It works perfect, my first impression, WOW!


There are few conditions which need to be checked as well, but I don't know how to do this in VBA, and therefore your help will be appreciated:

1
The check boxes functionality can be skipped.


2
The cells filled in worksheet 'Transfer' are based on combination of co-ordinates of 'Row-Column' and Group Number for each row taken from 'Setup'. So I guess it needs a loop which will be based on the the number of rows from Group 1 to last Group.

While filling the values from worksheet 'Setup' to worksheet 'Transfer, it should do it individual row column wise and if there are any mismatches (given below example), then an error message, and it should clear out what is filled.

Eg. Worksheet 'Transfer' Column C, Group 1 has 5 rows
Worksheet 'Setup' Group 1 has 4 rows

For the Given Columns:
-The setup of worksheet 'Setup' is fixed i.e the header labels mentioned in the cells. It is possible the user can add a row in any of the Groups.
-It will be based on a loop, depending on the number of filled rows in 'Setup'
-It will get Group 1, co-ordinates of row-column and the value
-Go to worksheet 'Transfer', and check for the given co-ordinate of row-column exists Group 1.
-If yes then place the value in the given co-ordinate of row-column
- Go next record
-If not, then give error message and stop process.
-repeat the above process.


I hope I have explained it. If not clear, let me know.


Thanks again for your help.



Regards
Don

 

[Nebu]

Hi:

The values are populated in the transfer worksheet based on an index match formula. I have build-in flexibility in the rows , but the columns are static , I believe it would not be an issue since for each set the column range is selected separately. In short, if you could maintain the same group order in transfer sheet as in the set-up sheet the macro would work fine even if you increase the no.of rows. Let me know with questions if any...

 

[inddon]

Hi:

The values are populated in the transfer worksheet based on an index match formula. I have build-in flexibility in the rows , but the columns are static , I believe it would not be an issue since for each set the column range is selected separately. In short, if you could maintain the same group order in transfer sheet as in the set-up sheet the macro would work fine even if you increase the no.of rows. Let me know with questions if any...

Hello Nebu,


Thank you for your response.

The columns and rows co-ordinates, where the values should be copied in the worksheet 'Transfer' are given in the worksheet 'Setup' (please refer the jpeg attachment: 'Snapshot 1- Actual.jpeg' for reference).

In case, if the User adds another column in worksheet 'Transfer' then he has to reflect the same in 'Setup' (please refer the jpeg attachment: 'Snapshot 2 - Addition of column.jpeg' for reference).

The same goes for row change as well( attachment: 'Snapshot 3 - Mismatch of total rows in Group.jpeg')

If the User adds a new row in the 'Transfer' for Group 1 same column, then he has to do the same in the Setup worksheet. In 'Setup', add the row in Group 1 and updated all the Row Numbers accordingly (the subsequent rows in Group 2 and 3 will be increased by the above action).

Sounds complex for me, but I am curious to look at the solution and learn.

Thanks again for your time and help.

Regards
Don

 

[Nebu]

Hi:
Please find the file, I have build-in the flexibility you have asked for...

 

[inddon]

Hi:
Please find the file, I have build-in the flexibility you have asked for...

Hello Nebu,

Thank you for your time and for your solution. I had a quick look and it works wonderful.

I will do a complete test later today and will let you by tomorrow.

Regards
Don

 

[inddon]

Hi:
Please find the file, I have build-in the flexibility you have asked for...

Hello Nebu,

I came across a small problem in the below test case:

1. In the worksheet 'Transfer', I added a row (a 5th one), in 'Group1', whereas in Setup there are only 4 rows.
2. There is a mismatch in Group 1: Transfer sheet has 5 rows and Setup has 4 rows.

When I debug the code it executes the code section k = 4 and goes to K=8 section, it places #N/A on the 5th added row in 'Transfer sheet for Group 1' and gives a message "Mismatch in Group 2", instead of "Mismatch in Group 1"

I have attached the file for your reference.

Could you please advise?


Thanks & regards
Don

 

[Nebu]

I have fixed the issue , but I would suggest to keep the setup tab unchanged, the macro need to have a reference point which is standard. If you mess up setup tab it will throw the macro off , just go through my code and try to understand the logic I have applied its fairly simple.....

 

[inddon]

I have fixed the issue , but I would suggest to keep the setup tab unchanged, the macro need to have a reference point which is standard. If you mess up setup tab it will throw the macro off , just go through my code and try to understand the logic I have applied its fairly simple.....

Hi Nebu,

Thank you for your solution. It works good as expected.

I did a test and found the below. It intrigues me. I did not think I will come to such a testing and see the various scenarios:

1. The rows for Group 1 in Setup is D4 to D8 and the first block of loop for Group 1 is as below which will execute from D4:D8

For i = Sheet2.Range("D4").Value To Sheet2.Range("D8").Value

2. When I insert a row in Group 1 in worksheet Transfer and Setup worksheet
Now the new range for Group 1 in Setup is D4:D9

When I am in debug mode [F8] the above loop code for Group 1, finishes at D9. It comes to the below code Group 2 loop, and then he inserts the value of Group 1 in Transfer:

For i = Sheet2.Range("D9").Value To Sheet2.Range("D30").Value

3. If I insert rows in Group 3 at the end, then the below code will execute only upto D54

For i = Sheet2.Range("D31").Value To Sheet2.Range("D54").Value

I have attached the workbook for your reference

How can this be made flexible using the combination of row-column given in the Setup?



Could you please advise.


Thanks & regards
Don

 

[Nebu]

Hi:

I have made it as flexible as possible, but mind you your report is really volatile and sensitive. I hope you have a valid reason to make it as flexible as it is now ....