Taking new data from one list and adding it to another list via VBA

I have 2 sheets: "MasterInventory" and "InventoryFeed". "MasterInventory", is my full list of all the item I sell on Amazon.com. "InventoryFeed" is populated when I download an update from amazon and paste the data into it. Each list has about 30 columns and each list uses identical headings in the top row. The third column in each sheet is for the product's SKU.

What I'd like to do is loop through each row in the "inventoryFeed" sheet, assign it's sku (the value in column 3) to a variable, and then copy the entire Row. Then I'd like to switch to the "MasterInventory" sheet, and look in column C for the identical SKU. If it is found, I'd like to overwrite the entire line by pasting in the data that was copied earlier. If the SKU is not discovered, I'd like to paste the information into the next blank row in the "masterInventory" sheet.

After the whole thing is looped through, I'd like to sort the MasterInventory Sheet from A to Z by the product's SKU.

Any help would really be appreciated!

VBA Forum Sample.xlsm

OK, I attached a sample workbook. The full workbook has many more sheets, but all that's necessary here are these two.

In plain English, here's what I'm after...

I sell stuff on Amazon.com. I want to use the data that they store on their servers (item weight, size, category, fulfillment cost etc.) to analyze what's profitable and what's not.

In the attached sample, the sheet "FBA Fee Feed" is what I get off Amazon's site. I basically download it and it opens automatically in a new excel sheet. I then copy and paste it into the "FbaFeeFeed" sheet of my workbook.

I could have simply used Amazon's data alone and not dealt with creating a second sheet (here named: "Full Inventory"), but Amazon only provides data for products that are actually in stock in their fulfillment center. Once any of my products sells out, it will no longer be in their data feed, which doesn't do me much good.

So the whole idea is to establish a worksheet that has a full listing of that I sell, whether it's in stock or not.

I mentioned in my initial post that when the macro finds a product from the data feed that is already in the "Full Inventory" sheet, that it should overwrite the entire line. The reason for this is sometimes there can be slight changes in one or two cells, so just overwriting the existing data keeps everything up to date.

One last thing... I mentioned in the original post that I want to compare "SKU's" from each sheet. I used that term to avoid confusion, but now that I've uploaded that sample workbook, I'll be more specific. The actual item I want to compare is called the "ASIN", which is Amazon's unique designation for each product that's on the site. Regardless of what's it's called, the piece of data that is used to compare one sheet against the other is on column "C" in both work sheets.

Wow! I just discovered a new definition of "awesome". I think you were able to solve the problem faster than I could explain the problem!

Could I ask you to do just minor tweak?

After the looping is done, could you sort the "Full Inventory" sheet by my SKU (which appears in column A) from a to z?

Thanks !

Sweet! Reputation points are on their way!

Thanks again!

I hate to be seen as going to the well a second time, but I wonder if you or someone else could add just one more tweak to the code.

Amazon doesn't especially like quotation marks in seller's SKU's, so they will always take a SKU like this:

Furn Glide - Felt 1 1/8" x 16

and turn it into this: Furn Glide - Felt 1 1/8" x 16

I've been handling this up till now with a simple "substitute" formula, but it would be much more elegant if the conversion were done via VBA.

How can I accomplish this while the code is looping through?

Worked like a charm Olly. Thanks!

One quick observation...

I noticed when stepping through the code that it searches every cell on the sheet for the weird characters, while they only appear in Column A. Is it possible to just have it do the replace in that column? This would speed things up quite a lot I think.