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

 

[mumps]

Try:

Sub NameFill()
    Sheet1.Range("A2:A" & Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp).Row) = Sheet2.Range("B7")
End Sub

 

[Mr2017]

Hi Mumps

Thanks for the prompt response.

That code works on the existing data.

But if more students are added to column B in Sheet 1, and a new 'Class' letter appears in cell B7 in Sheet 2, the code over-writes all the Class names in column A in Sheet 1, including 'Class' A that was previously added (please see the data below - I added in Student names Brenda, Barbara and Brie and changed the 'Class' in cell BY in Sheet to to 'B' (from 'A') then ran the code, but instead of putting the letter B against the new names and leaving A against the old names, it over-wrote everything in column A in Sheet 1?

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Class[/TD]
[TD="class: xl65, width: 64"]Student [/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Jody [/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Brenda[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Barbara[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Brie [/TD]
[/TR]
</tbody>[/TABLE]

Do you know how to modify the code that that it doesn't over-write the previously entered Classes in column A? If you'd like me to clarify further, please let me know.

Thanks in advance.

 

[mumps]

Try:

Sub NameFill()
    Dim bottomA As Long, bottomB As Long
    bottomA = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
    bottomB = Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp).Row
    Sheet1.Range("A" & bottomA + 1 & ":A" & bottomB) = Sheet2.Range("B7")
End Sub

 

[Mr2017]

Ok, thanks Mumps! That works as expected.

 

[mumps]

You are very welcome.

 

[Mr2017]

Hmmm....quick question.

Although the example I gave involves two different sheets, in reality there are two different workbooks.

When I modify the code you wrote and add it to the files I have, I get an error.

In the original code I wrote prior to this, I'd always activate a workbook before referring to a Sheet in it.

Eg the file with the data that needs to be updated is called XYZReport and the file that has data that needs to be imported is called NewFileWB.

So when I first import the 'NewFileWB' I set it as the 'ActiveWorkbook' as you can see in the code below (this is a snippet of the code to illustrate how I'm able to switch between the workbooks) then I alternate between the workbooks by using the

Dim XYZReport As Workbook
Dim NewFileWB As Workbook


'This code sets NewFileWB as the ActiveWorkbook
Set NewFileWB = ActiveWorkbook

'This code activates Sheet 5 in the XYZ report then deletes everything in it.
XYZReport.Activate

Sheet5.Activate
Sheet5.Cells.Select
Selection.Delete



'This code re-activates 'NewFileWB' - the file that we're importing the latest data from
NewFileWB.Activate

'This code then copies everything in the 'Current region' from cell A8. The cell with the 'Class' is cell B4

Range("A8").CurrentRegion.Copy

So my question is, do you know how to re-write the code you've written if the 'Class' name is being retrieved from a different workbook, please?

I thought this would work, but I get a Debug error which highlights the first variable (bottom A) when I try to run the code....

Dim bottomA As Long, bottomB As Long
bottomA = XYZReport.Sheet1.Range("A" & XYZReport.Sheet1.Rows.Count).End(xlUp).Row
bottomB = XYZReport.Sheet1.Range("B" & XYZReport.Sheet1.Rows.Count).End(xlUp).Row

XYZReport.Sheet1.Range("A" & bottomA + 1 & ":A" & bottomB) = NewFileWB.Sheet1.Range("B7")

Thanks in advance and thanks for your help, so far!

 

[mumps]

You don' have to select and activate the workbooks for most VBA actions. You can make direct references to workbooks, sheets and ranges in the macro. What are the full names including the extension (xlsx,xlsm) of the 2 workbooks? What is the name of the sheet in NewFileWB containing the data you are copying? What is the name of the sheet in XYZReport where you want the copied data to be pasted and where in that sheet do you want it pasted? This sheet should also have headers in row 1 starting in column A, for example: "Class", "Student", etc.

 

[Mr2017]

Hi Mumps, thanks for your response. I'll send you a private message.

 

[Mr2017]

Hi Mumps - I've just sent you the private message.

Thanks in advance.