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]

It wouldn’t be every promotions file.

Just the one that a user wants to over-write.

So if they’d just made changes to the Coffee Department for example (in the web-based application), after they’ve downloaded the file with the changes to the “Downloads” folder, the macro would direct them to the “Downloads” folder, when they’ve activated it, then they’d select the new Coffee file from the Downloads folder and that file would over-write the existing Coffee data in the file that the data is imported to.

An alternative way to describe it would be if the existing data in the CTT file had everything price at $2 for Coffee, but there was a new file with everything priced at $1 for Coffee, then a user would be able to import the file with the $1 prices and everything in that file would over-wrote the existing data.

This would avoid an issue of where some existing products in the CTT file that were priced at $2, but should now be $1, weren’t amended / updated, because they were skipped over instead of over-written.

Does that make sense?

 

[Mr2017]

Also, for clarity, it would be a separate macro to the existing ones, as it would only be used if a user wanted to over-write data for an existing Department....

 

[mumps]

That would work for Sheet1 because it contains the word "Coffee" in column A. It won't work for Sheet3 because it doesn't contain the "Department" column with the word "Coffee". We would have to add another column to Sheet3 that would contain "Coffee". Is that OK with you?

 

[Mr2017]

Yes, that’s absolutely fine with me.

Thanks in advance.

 

[mumps]

I'm having some problems with my computer. I'll get back to you as soon as I can.

 

[Mr2017]

Ok, no problem.

Thanks for the update.

I need to send something through tomorrow, so if your computer doesn’t work today, I’ll try to piece together bits of code that work.

Thanks for your help, so far. It’s been greatly appreciated.

 

[mumps]

Click here to download your file.

 

[Mr2017]

Oh, awesome!! That's amazing!!! Thanks ever so much!!

 

[mumps]

You are very welcome.

 

[Mr2017]

Hi Mumps

I'm running the code on the workbooks I have at work now.

But I get an error that says "desWS1 = Nothing" and "Subscript out of range" when it gets to this line: Set desWS1 = wkbDest.Sheets("Sheet1")

Do you know why that may be?

I assume that "desW1" is the destination worksheet? and "wkbSource" is the file that's being imported?

Thanks in advance.