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

Class	Student
	Jack
	Jane
	Jody

<tbody>
</tbody>

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

 

[mumps]

This is correct.

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

Do you have a sheet named "Sheet1"?

 

[Mr2017]

Thanks for the prompt response!

The Sheets I have are called Sheet 1, Sheet 2, etc in the back of the house / VBA editor, but they have different names in the front of the house!

So I'll rename them and then I think it'll work!

Thanks again for the prompt response!

 

[mumps]

My pleasure.

 

[Mr2017]

You were right!! I've renamed the Sheets and it's importing and appending data as expected!

This is very exciting!!!

The only issue is with the Replace Sub where it stops when it gets to the line ".Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete" and it says "Cannot use that command on overlapping selections."

To give more context - this is the part where it selects a Department in column A then deletes that Department. It's selected the Department, but then stopped there.

The data is in row 4 now rather row 2, but I have amended the cell reference from A2 (where the Department name originally sat) to A4, where it sits now.

This is the code

With desWS1.Range("A4").CurrentRegion
.AutoFilter 1, Category
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With

Do you know why that may be? I guess it's something else that I need to change in my spreadsheet?

Thanks for your suggestions and the code, so far! This has been incredibly helpful!!!

 

[Mr2017]

I also had to rename "Department" to "Category" but I don't think that would have be a problem?

 

[mumps]

Changing the headers won't have any effect on the macro in this case. Therefore, the code should be left unchanged:

dept = .Range("B4").Value
With desWS1.Range("A2").CurrentRegion
    .AutoFilter 1, dept
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With
With desWS3.Range("A8").CurrentRegion
    .AutoFilter 1, dept
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With

Try that and see if it solves the problem.

 

[Mr2017]

Thanks for sending that through.

I tried it, but it still stopped at the same line ie .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

However, I tried to change "EntireRow.Delete" to "Delete Shift:=xlUp" with reference to desWS1, then left the second "EntireRow.Delete" as it is and the code then just worked!!

Strange! Must be the file I have!

I'm just going to test a few iterations of the replace code ie putting a Department below another, then replacing it, putting it in the middle of two Departments then replacing it and also putting it at top of another Department, then replacing it.

But this has been INCREDIBLY helpful, so far!

 

[mumps]

Glad it worked out.

 

[Mr2017]

Woooooo!!!!

You have made my week!!! Thanks again, Mumps!! Hats off to you, Sir!

All combinations worked! I'm incredibly grateful!!

I have three final questions, if I may, please:

1) Do you know how I can change the Sheet names from the "front of the house" to the "back of the house", in case a user renames one of the "front of the house" names?

Eg with the line below, I tried to change it to With wkbSource.Sheet1 (as there is only one Sheet in the source file and it's called Sheet 1 in the "back of the house" - but that change didn't work, so I'm still using the "front of the house" name. I'd be grateful if you could clarify where I went wrong with the code here, please?

With wkbSource.Sheets("Sheet1")

2) Do you know how I can combine the CopyData and ReplaceData subs into one, so that they don't have to run separately?

At the moment, I've created a separate button for each one, but I'd like to put them both into one button....

3) Sometimes the file freezes if a user makes a mistake eg if they import the wrong file - the "Clear data" button fixes that, but if you know whether it's possible to prevent the freeze, I'd be grateful.

Thanks again for your help - it's been much appreciated!

I'm going home back but will be back on Monday.

I hope you have a great weekend!

 

[mumps]

Are you saying that you want to rename "Sheet1" of every Promotion file that is imported to "back of the house"? If we combine the CopyData and ReplaceData macros into one, the combined macro needs a way to distinguish which of the two actions it's going to perform, copy the data or replace the data. It could prompt the user as to what needs to be done by entering "Copy" or "Replace" as a response. Would that work for you? Regarding the "Freeze" problem, in order to fix it, we would need to know what is causing it and also what you mean by "freeze".