Help Needed Moving data and rows from different sheets while updating prior data.

Version: Office 365

What I'm trying to accomplish: I have uploaded a sample sheet to play with. None of this is the actual file just something to go off of and the types of columns I have.

I posted in general help and then realized that maybe this isn't so general of a request. So I am posting here as it seems a better fit.

I will attach a book and included is a copy of someone's crack at doing it with VBA. I am no expert in VBA and they are so close and I am unsure of how to modify this to make it work.

Here is what I am trying to do:

Everyday I paste raw data into my datasheet and there can be a lot of it. So I then have to manually go through and identify any cases that are currently being investigated on my master sheet.

What I'm hoping for is a VBA macro that I can have as a button to update the data from my data sheet. This will save me loads of time. I have tried to do this with Vlookup and Xlookup. I have made a macro to trim all the data I don't need in the other columns and what I'm left with is basically the ones that are seen in my book.

I am hoping to be able to click a button and run some VBA that will sift through the data for me and update my master sheet. If the service case is on the master sheet all I want it to do is look at the data sheet and replace the aging days number with the updated value. If the service case is not on the master sheet I would like for it to add the entire row to the bottom of the table as a new record. I can filter from there.


I'm really hoping someone with much more experience than I can recognize the issue and provide a fix.

Thanks for the help if anyone wants to attempt. I would be so grateful to have this off my plate. Also Would love to learn how it works so that I can add this to my toolbelt in other areas should they arise.

Thanks so much for anyone willing to take a crack at this.


** Bonus: if marked complete the row moves off the master sheet to the completed tab and counts what shift completed it. The columns are in the table but right now I am really hoping to get the data sifted through.

See if this is how you wanted.

Please Login or Register  to view this content.

This is working for the most part. What I am having trouble with now is that sometimes the script is causing the records to be duplicated several times. I am not sure why this is happening. How would I make it go back through the script and ignore anything that was already there if it is a duplicate ID? What I did to create this error is in testing I loaded some data in and then ran the script via the button. Then I changed an aging day on the datasheet to simulate the advancement of days on a record. Then went back and checked the master sheet. After running the script again by clicking the button it adds the whole file again. I have stepped through the code above with the debugger and cannot seem to figure out how to tell it to stop doing this. Any ideas as this is almost perfect.

Originally Posted by Raistlin8522

What I am having trouble with now is that sometimes the script is causing the records to be duplicated several times. I am not sure why this is happening.

Because the code is written based on the assumption that NO duplicate within a sheet.
If duplicate within a sheet, take only the first data.
If not, you need to show me your desired result with the duplicates.

Please Login or Register  to view this content.

Originally Posted by jindon

Because the code is written based on the assumption that NO duplicate within a sheet.
If duplicate within a sheet, take only the first data.
If not, you need to show me your desired result with the duplicates.

Please Login or Register  to view this content.

I think the problem is with how I am moving your code to my actual file with sensitive information inside it. Everything seems to be the same but for some reason on the file that I move the code into it is creating a duplicate each time I run the VBA to test it. I am sure that it is me doing something incorrectly. I am not very good at VBA so I am not sure how to identify the area that I am having trouble with.

Both files have [MasterSheet]
Both files have [DataSheet]
Both files refer to [Table1] on master sheet.
The only thing I see that is different are slightly different Column Names, however the values are in the same order.

Yet when I run the code in the workbook above it runs perfectly.
When I place the code in the actual workbook it runs well the first time. Then if I change one value [aging days] it creates duplicates of almost every row below that point and of that record.

I am thinking that I am missing something in the use of the code or that I am not applying it to my new workbook correctly. Do the column names matter and if so how would I change that? All Columns are in the same order {Case ID} > {Age}>{Description}> {Comments}.

Steps I took:
I confirmed there were no duplicate case ID's.
Then I ran the macro to update the sheet.
Confirmed no duplicates again ( there were none at this point)
Changed the aging days on one record from 5 to 25
Ran the Code again and now I have two records one is at 5 days aging and the other is at 25 days aging.

Where am I messing this up at. Id love to show the actual data but cannot. And it seems to be working in the sample workbook. This to me means that I am the issue, sadly. It is my execution that is causing this to fail. Any additional help to get this transferred over to the book correctly would be amazing. But I cannot duplicate the issue in the supplied book either. Only in the book that I move the code to, that I cannot share.

Thank- you for all the help. Hopefully this will help someone else as well.

If it still have duplicate after the code, column reference or data layouts would be differnt.
So, no way to fix unless I see your workbook.

You have extra headers(columns) in DataSheet with no data, so that the code replaces existing value with empty for those columns.
Just one line change in bold.

Please Login or Register  to view this content.

Hello,

So i was able to figure out how to fix my issue using the code provided above. Then as everything started working correctly I noticed that I needed to remove any items in my "MasterSheet" table that was not also in the "DataSheet". This would happen because the case was completed and is no longer in the report.I have been trying to figure out a way to add a step to this that will go back and check if any of the items on "MasterSheet" are no longer in the "DataSheet" and remove them. Ideally I need to figure out a way to look at Column A "service case ID" and if it is not on both sheets.. remove it from "MasterSheet" as a completed item. NOTE: the problem before was not in the code it ( it worked just as it was supposed to. It was in the way I personally had my data coming in. It was a general text not a number. I'm really hoping someone can help here

The sheet above contains the working code and format of the tables.

Thanks so much in advance.

Originally Posted by JohnTopley

I tried to simulate what you did (or I think you did) using the file from post #2 by simply changing an "aging" day value. The only change on the Mater was that of the "aging Day". Data was not repeated.

Please post the file you used and show the before/after states.

Hi John,
Sorry about not posting the resolution to my problem in a more timely manner. The issue was in the formatting. Another macro in the book was causing the ID's to be turned to text and not a number format. Once I changed that everything resolved. Now if I can figure out my next issue I should be good. Again thanks for your replies.