Name fill - VBA - simple example

[Mr2017]

Hi

Quick VBA name fill question

I've got the data below in Sheet 1 of a spreadsheet.

I'd like to copy the 'Class name' from Sheet 2 then fill it in the 'Class' column in Sheet 1 (column A).

Assuming that the class name is in cell B7 in Sheet 2, I would have thought that the code below would copy the Class name from Sheet 2 then filled it in against all the names of the Students in Sheet 1. But it isn't working, as expected. Any ideas why? The result should be that the letter 'A' appears in cells A2, A3 and A4.

In reality, I'll import additional files with Students, then add the 'Class' name in column A for the additional Students. But I'm not sure why the code is not pasting data against the Student's names?

Thanks in advance


[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Class[/TD]
[TD="class: xl66, width: 64"]Student[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jody[/TD]
[/TR]
</tbody>[/TABLE]


Sub NameFill()


Sheet2.Activate
Range("b7").Copy

Sheet1.Activate
Range("b1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).Select
Selection.End(xlUp).PasteSpecial (xlPasteAll)

End Sub

 

[Mr2017]

Hi Mumps

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. Apologies. But please use the button called "Button 4" if you'd like to import another file.

NB the files with the word "Promotions...." at the beginning must be saved to the "Downloads" folder for the existing macros to work.

To recap, I'd like to get the Department to appear in column A when the data is imported using the macro buttons in Sheet 1 of the CCT file.

If you know how to modify the original code you wrote in order to do this, please let me know.

Thanks in advance.

Thanks

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

 

[mumps]

Thank you for the files. Rather than trying to modify your existing macros, it would be easier if we can start from the beginning. Looking at the Promotions files and at the end result in the CTTReport, it looks like you want to copy the data starting in row 9, columns B:AB of the Promotions files to columns B:AC in the CTTReport. Then you want to fill the corresponding cells in column A of CTTReport with B4 of each Promotions file. Is this correct? Your code asks you to open one Promotions file at a time. Is this what you want to do or would it be OK if the macro automatically opens all the Promotions files in the "Downloads" folder without prompting you and copies the appropriate data fom each file to CTTReport ?

 

[Mr2017]

Hi Mumps

Thanks for your response.

Your assumption is correct, with the exception of a couple of tweaks.

i) the data I'd want to copy from the Promotions files is from column A:AB rather than B:AB, but I think you've already guessed this.
ii) I'd want to copy data from row 8 (the title row) in the Promotions files then paste the data into Sheet 3 of the CTT report (Sheet 3 is a back up of the data, in case a user accidentally tampers with data in Sheet 1 of the CTT.
iii) I'd then want to copy the data from row 9 in Sheet 3 into Sheet 1 of the CTT report, and as you correctly assumed - get the Department into column A of Sheet 1.
iv) Another thing I'm looking at is a separate 'over-write' button which would allow a user to over-write data for an existing Department in Sheet 1 (and ideally Sheet 3) of the CTT file if a user chose a file with a specific Department from the "Downloads" folder. Eg if there was a "Promotions - Frozen" file and "Promotions - Frozen - New data" file, I'd like to be able to select the ""Frozen - New data" file and use it to over-write data that was in the CTT file that was based on the "Promotions - Frozen" file. If you know how to do that, please let me know.

I'd prefer to avoid the macro opening all the "Promotions" files in the "Downloads" folder, as some of them may not be used.

Thanks for your help, so far. If you have any questions, please let me know.

 

[mumps]

In your existing macros, I noticed some questions that you asked that seem to indicate that there is a specific order that you want to use to open the files. If this is correct, could you please explain in detail what that order is? In order to do what you described in your question # iv, we would need a unique value to identify the rows that would be overwritten. Are the Product ID's unique across all the Promotions files?

 

[Mr2017]

Re your first question, yes - I created the macros so that the first file imported is always pasted 'at the top' ie row 8 in Sheet 3 and row 3 in Sheet 1. If someone then wants to import an additional file, then the additional file (or files) would be appended at the bottom of the first set of data that was imported - that's why the buttons are separate, at the moment.

Re your second question - yes, the product IDs are unique.

Please let me know if you have any other questions.

 

[mumps]

Click here to download your file. I think that the buttons are self explanatory. Please let me know if you have any questions. Please note that both Sheet1 and Sheet3 always start out with headers in rows 2 and 8 respectively.

 

[Mr2017]

Ok, awesome!! Thank you!

It works well!

The only question I have is about the "replacement code." If I import the first Coffee, then the second Coffee file (with new data), the second file doesn't over-write the data from the first file. Do you know how to amend it, so it does?

Thanks

 

[mumps]

The "replacement code" depends on the Product ID's. The ID's in the second Coffee file (with new data) have to match the ID's in the first Coffee file that was imported. This is how the macro knows which data to replace. Do the ID's of the products you want to replace match in both files?

 

[Mr2017]

Ah, ok! I see what you’ve done there! The IDs didn’t match which is why the data didn’t get replaced.

But do you know if it’s possible to delete every row for data containing coffee in the CTT file (if “Coffee” is the value of cell B4 in the “Promitions...” file?) then add in the new “Coffee data”

This is because when changes are made to the new file, a change could be made to a product that already exists in the CTT file eg a price change. And in that case, we want to have the latest price for the product....

Does that make sense?

 

[mumps]

So you want the macro to check cell B4 of every Promotions file that is imported and if that value exists in the CTT file, delete all the rows that contain that value and replace them with the data in that Promotions file. Is this correct?