Transfer data from 2 sheets to another one depending on a given customer number

[Philippecolass]

Hi there!

First time posting here and very new to VBA programming.

Anyhow, I have a big challenge. I have a workbook with data from 2 sheets (Manual Data and Data from System). In the same workbook, I have a a 3rd sheet (Summary), where the data from these 2 sheets should be transferred. Finally, all data from the 3rd sheet (Summary) should be transferred to a 4th sheet (Master) with only open cases depending on the status.

Please find attached the excel workbook. Note that there is no data as all data is confidential.

Steps:

1. Search in the sheet called "Data from System" for customer numbers "296898" and "297055" from the column called "SHPRNO" and only transfer the customer data from this customers.
2. Summary sheet shows which data should go in which column.
3. Note that only the data without color should contain data from "Data from System".
4. Column H in "Data from System" in green should get data from the sheet called "Goods" depending on the data in the G column in "Summary".
5. Column AP in "Data from System" should get the year from column AQ in "Data from System".
6. Column AX in "Data from System" should always says "DVS".
7. Column AZ is manually with "Open" or "Finished".
8. Rest of the Columns in "Data from System" should be filled in manually.
9. The sheet called "Manual Data" it is all filled manually.
10. Once this is done the sheet called "Summary" should get the data from the sheet called "Manual Data" and put it in the bottom of the data already fetched from "Data from system" in all columns in "Summary".
11. If there are any duplicates based on the "BL" number, the macro should take the data from the sheet called "Data from System" and the sheet called "Manual Data" to fill in the info in the sheet called "Summary".
12. All the data with status "Open" column AZ from the sheet called "Summary" should be copied to to the sheet called "Master".

I hope this is clear

Thanks a lot in advance.

[sourabhg98]

Hey there,

Please Login or Register  to view this content.

I have written steps 1 to 9 in this code.
Steps 1 to 3 are not complete, you can easily fill them up yourself for all other columns following as I did.

Also I am not able to understand 10th and 11th step, so if you could please elaborate them so that I can help you.

[Philippecolass]

Hi sourabhg98,

Thanks a lot for this, unfortunately step 1 does not seem to be working completely.

There are around 552 customers with account numbers "296898" and "297055", however, when I run the macro, I only get 1 account in the "Summary" sheet.

I have not yet checked the rest of the steps, as the first step is key.

Could you check on this?

Once gain, thank you!

[sourabhg98]

Okay, that was my mistake.
Check this out.

[Philippecolass]

Thanks again

I got it to work like this, because it came with some error every time I ran it.

Sub transfer_info()

Sub transfer_info()

'steps 1 to 3
For n = 2 To Sheets("Data from System").Range("L10000").End(xlUp).Row
If Sheets("Data from System").Range("L" & n).Value >= 296898 And Sheets("Data from System").Range("L" & n).Value <= 297055 Then
k = Sheets("Summary").Range("A10000").End(xlUp).Row + 1
Sheets("Summary").Range("A" & k).Value = Sheets("Data from System").Range("D" & n).Value
Sheets("Summary").Range("C" & k).Value = Sheets("Data from System").Range("AG" & n).Value
Sheets("Summary").Range("E" & k).Value = Sheets("Data from System").Range("AQ" & n).Value
Sheets("Summary").Range("P" & k).Value = Sheets("Data from System").Range("Z" & n).Value
Sheets("Summary").Range("Q" & k).Value = Sheets("Data from System").Range("X" & n).Value
Sheets("Summary").Range("T" & k).Value = Sheets("Data from System").Range("BR" & n).Value
Sheets("Summary").Range("AA" & k).Value = Sheets("Data from System").Range("BS" & n).Value
Sheets("Summary").Range("AC" & k).Value = Sheets("Data from System").Range("AI" & n).Value
Sheets("Summary").Range("AD" & k).Value = Sheets("Data from System").Range("AG" & n).Value
Sheets("Summary").Range("AI" & k).Value = Sheets("Data from System").Range("T" & n).Value
Sheets("Summary").Range("AJ" & k).Value = Sheets("Data from System").Range("R" & n).Value
Sheets("Summary").Range("AK" & k).Value = Sheets("Data from System").Range("S" & n).Value
Sheets("Summary").Range("AL" & k).Value = Sheets("Data from System").Range("W" & n).Value
Sheets("Summary").Range("AM" & k).Value = Sheets("Data from System").Range("U" & n).Value
Sheets("Summary").Range("BB" & k).Value = Sheets("Data from System").Range("BD" & n).Value
Sheets("Summary").Range("BC" & k).Value = Sheets("Data from System").Range("L" & n).Value
'Step6
Sheets("Summary").Range("AX" & k).Value = "DSV"
'Step4
On Error Resume Next
Sheets("Summary").Range("H" & k).Value = Application.WorksheetFunction.VLookup(Sheets("Summary").Range("G" & k).Value, Sheets("Goods").Range("A2:B30"), 2, False)
Else
End If
'Step5
Range("AP" & n).Value = Year(Range("AQ" & n).Value)
Next n
End Sub


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Now, step 10, should copy the data from the sheet called "Manual Data" at the end of the data that has been just copied from "Data from system" to "Summary" from data. This step should also check that the numbers from column F do not repeat in the "Summary" sheet. If there is a duplicate give a message with the number in "F" column that is a duplicate, so it can be deleted from the "Manual Data".

Also, I have noticed that when I click on the macro button twice, it copies the data again to "Summary" under the data that has just been copied to "Summary". Could it be possible to stop it from doing that so the process only runs once? That is because this macro should only update new rows, not the whole data.

I hope you know what I mean.

[sourabhg98]

Okay, Check attached.
First click the button. All values will be updated.
Now, on the Summary sheet input some value in Column F. Now, in the manual sheet type the same value to create a duplicate.
Click on the button again. The value which is duplicated i.e. found on Summary sheet already would not be entered. On the manual sheet it will be highlighted in Red. Remove that duplicate from manual data and change the color of the red cell back to normal manually.

Also only new data entered in the sheets will be updated, not the whole data as earlier.

Note- I am using 'Data from System' cell >> BV1 and Manual data >> BA1 as helper cells for the macro. You can edit these to any unused range you want.

[Philippecolass]

Hi again,

Thanks again a lot for your help.

I will provide you with a full data sheet.I think we are very close, but there are small issues.

1. The "Manual Data" sheet seems to be only copying the tittle from that sheet (Please see attachment)
2. Could you delete step 4? I think I will just run a formula there instead.
3. Last step to finalize this.

In the sheet called "Summary", there is a column with a title "Status". This column determinates if a row should be copied to the sheet called "Master". So if it says "Open" in that column, then the info of that row should be copied to "Master", otherwise it will just say "Closed" and it will not be copied to that sheet.

I hope this is clear.

Cheers!

[sourabhg98]

Okay, for the last step I need to know that how should the copy paste be done?
I mean can it be something like deleting all the existing data on the Master sheet and then updating all the data from the "Summary Sheet"?

[Philippecolass]

Every time the data is copied, the macro should update the existing data in the sheet "Master" with the data from the "Summary" sheet. Only rows with a status of "Open" should be copied/updated from "Summary" to "Master".

Please let me know if you have any further questions.

Thank you in advance.

[sourabhg98]

Okay now I think its all done.
I have added to macro. The macro deletes all existing data in Master Sheet and then updates the whole data again.
I would also like to mention again that Cell BV2 of Data from System shows the number of rows that have been updated. So every time macro executes it continues from the next row number as mentioned in the cell BV2.
You can always change it manually while testing to 1. If you put 1 there, macro will update all data from row 2. This way you can test the macro again and again.
Similarly cell BA1 of Manual Data is the last updated row of Manual data sheet.

[Philippecolass]

Thanks for the info.

I think you attached the wrong file

Could you check on this?

Cheers!

[sourabhg98]

Oops, my bad...check this one-

[Philippecolass]

Hi Sourabhg,

Is there any way to change the code, so it copies all data from "Manual data" sheet to "Summary" every time the button is clicked on? So Instead of copying only new rows, it should always copy all content, of course, without the tittles.

Thank you!

[sourabhg98]

Hi,
Really sorry for my late reply. I was not busy but just don't know how I skipped your query.
You could have send me a PM earlier.

Please Login or Register  to view this content.

So, I think you should replace the part of the code above in the old macro by the code below-

Please Login or Register  to view this content.

I hope that does that job.
Again apologies for the late response

[Philippecolass]

It works great! Thanks a lot

[sourabhg98]

Glad to know that worked!!

If you have no further queries please mark this thread as "Solved" from the "Thread Tools" above.