replacing old department data with new department data using VBA

[Mr2017]

Hi

I've got a sheet with data and the Department name in each row.

I'd like to replace data for a specific Department when I import data from a folder.

I've written the code to import the data. But I'd like to know if it's possible to write code that will search for a Department name in the worksheet I'm importing data into, then replace all rows that relate to that specific Department?

To illustrate, I've created a simple example below. It has two Departments - Chocolate and Cereals, over 5 rows.

There are TWO tabs - Sheet 1 and Sheet 2 - the only difference is the names and prices of products the Cereals in Sheet 2.

So is it possible to create code that OVER-WRITES the Cereals data, ONLY, in Sheet 1 (assuming that the new Cereals data has already been copied)?



Data for Sheet 1 (paste into cell A1)


[TABLE="width: 264"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Department[/TD]
[TD]Product[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Chocolate[/TD]
[TD]Choc 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Chocolate[/TD]
[TD]Choc2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Cereal[/TD]
[TD]Cornflakes[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Cereal[/TD]
[TD]All Bran[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Cereal[/TD]
[TD]Oats Porridge[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Data for Sheet 2 (paste into cell A1 of Sheet 2)

[TABLE="width: 257"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Department[/TD]
[TD]Product[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Chocolate[/TD]
[TD]Choc 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Chocolate[/TD]
[TD]Choc2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Cereal[/TD]
[TD]Muesli[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Cereal[/TD]
[TD]Lucky Charms[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Cereal[/TD]
[TD]Cheerios[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]


I've got code below which copies data from Sheet 2 then pastes it into Sheet 1. But I'd like to be able to replace specific Departments eg Cereals only or Chocolate only, when I've copied new data for that Department.

Thanks in advance

Sub replacementcode()


Sheet2.Activate
Range("A1").CurrentRegion.Copy


Sheet1.Activate
Range("A1").Activate
ActiveCell.PasteSpecial (xlPasteAll)
End Sub

 

[Mr2017]

Hi Mick

Thanks for your response.

Re your first question

(Why does the data on sheet1 and sheet 3 of "CCT.xlms" not appear to be  starting in the same column, I was assuming the "Product ID" is in  column "B)

- Sheet 1 in CCT is a customer facing tab, so the Department will need to be in column A, to enable them to select a Department and filter the data easily. At present, I've put in a formula to look up the Department but I'm thinking of getting the Department data into column A using VBA, as if you add in code to delete the "title" row when you add in a new Department (using "Button 4" in the CTT file), it deletes the formula in column A for that row, which means that the Department no longer comes up.

My code to transfer data from sheet3 "Promotions---" to sheet "CCT"  assumes the groups of data on all sheets relating to your "Input"  selection are all  the same size (for each selected  ID), are  each a  contiguous group, but not necessarily in the same row, is that  correct???

Yes - the only thing that's not contiguous is the Department name.

Please find below links to sample files. The right-click button on my laptop isn't working, at the moment, so I couldn't edit the macro assigned to the "Import Another File" button. But please use the button called "Button 4" if you'd like to import another file.

To recap, if data from the "Promotions - Coffee" file already existed in the CTT file, and you imported the "Promotions - CoffeeWithNewData" file, then I'd like the data from the "Promotions - CoffeeWithNewData" file to over-write the data from the "Promotions - Coffee" file. Does that make sense?



CTT file
https://www.dropbox.com/s/om9tb17q7zi911m/CTTReport.xlsm?dl=0
Cereals file
https://www.dropbox.com/s/knbk0iwpq2cz3dp/Promotions - Cereals.xlsx?dl=0
Coffee file
https://www.dropbox.com/s/m8msjnbqhtftvjn/Promotions - Coffee.xlsx?dl=0
Coffee with New Data file
https://www.dropbox.com/s/40sdl1v5hx2nv2q/Promotions - CoffeeWithNewDataxlsx.xlsx?dl=0

 

[Mr2017]

NB All files except the CCT file will need to be saved to the "Downloads" folder in order for the existing macros to work. Thanks

 

[MickG]

I'm sorry but, I'm afraid your thread requirements has now morphed in something far removed
from your original remit.
I have not got the time or commitement at the moment to look at this fully
So i wish you luck and hope someone else might be able to help you.
Regards Mick