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

Hope you had a good weekend!

No, I wouldn't want to rename "Sheet1" of every Promotion file that is imported to "back of the house" - I just want to use the "back of the house" sheet names eg if a user opened the destination file, they'd see a tab called "detail" but in the back of the house, that tab would be called "detail (Sheet 3)" for example. So it's the back of the house name (in the VBA editor) that I'd like to use, so if someone renamed the "detail" sheet to "data" for example, the code would still work, because it would refer to Sheet 3 instead of a tab called "detail" which would no longer exist, if it was renamed.....Do you know what I mean?

Re your suggestion about prompting a user to either copy or replace - yes - that sounds good to me! Thanks!

Re the "freeze" problem, it's something I've encountered if the wrong file is imported eg one of the sample files instead of the real ones, but I'll put in validation which will force users to pick the right file eg based on the number of columns....

Please let me know if you have any other questions.

Thanks

 

[mumps]

Click here for your file.
I have modified the reference to the sheets by using the sheet codename rather than the sheet name. This will take care of the problem if a sheet is renamed. However, it will create a problem if the tab order at the bottom is changed. So hopefully, the order will always remain the same. You now have only one button to click, "Import/Replace Data". When you click that button, you will next click an option button and then the "DONE" button to either "Import" or "Replace" the data.

 

[Mr2017]

Ok, great - thanks!

I'll download this at home tonight, then try it out at work tomorrow.

Thanks again!

 

[Mr2017]

Ok, this works. Thanks

 

[mumps]

You're welcome.

 

[Mr2017]

Hi Mumps

I hope you’re well!

I have a quick question: you’d mentioned importing data from all the files in a folder in one go....

Do you know if it would be possible to modify the CopyData sub to import data from all files in a folder with the following conditions?

I) the files were imported into that folder on a specific date eg today

ii) files would only be imported once eg if there was a “Promotions - Coffee” file and a “Promotions - Coffee - New Data” file, only the most recent file out of those two would be imported....

Please let me know.

Thanks

I’ll be offline now for a few hours.

 

[mumps]

The problem with importing all files in one go is that you would not be able to chose which ones you want to import and which ones you want to replace.

 

[Mr2017]

Ok.

I was thinking of importing all of them at once, then the option to replace them would be done separately on a case by case basis.

But if it’s too complicated, that’s ok- I just thought I’d ask, as I wasn’t sure.

Thanks again for your help!

 

[mumps]

My pleasure.

 

[Mr2017]

Hi Mumps

Quick question:

With the CopyData sub, do you know what code to write to check if a user has already imported the Category that they're trying to import and if so, then add a message box to warn them that they've already imported it?

TIA