Name fill - VBA - simple example

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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


ClassStudent
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
 
Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,589
Try:
Code:
Sub NameFill()
    Sheet1.Range("A2:A" & Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp).Row) = Sheet2.Range("B7")
End Sub
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?

ClassStudent
BJack
BJane
BJody
BBrenda
BBarbara
BBrie

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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

Well-known Member
Joined
Apr 11, 2012
Messages
9,589
Try:
Code:
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

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Ok, thanks Mumps! That works as expected.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2012
Messages
9,589
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.
 
Last edited:

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Mumps, thanks for your response. I'll send you a private message.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Mumps - I've just sent you the private message.

Thanks in advance.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,424
Messages
5,528,685
Members
409,830
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top