Name fill - VBA - simple example

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
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:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try:
Code:
Sub NameFill()
    Sheet1.Range("A2:A" & Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp).Row) = Sheet2.Range("B7")
End Sub
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Ok, thanks Mumps! That works as expected.
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
Hi Mumps, thanks for your response. I'll send you a private message.
 
Upvote 0
Hi Mumps - I've just sent you the private message.

Thanks in advance.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top