The Sample Data
Extract and open the file linked to in the Files Needed
section above. You'll find a workbook containing data about the 2017-2018
NFL season:
We'd like to copy the table for each division into the appropriate worksheet.
Referring to a Fixed Range of Cells
We'll start by looking at how to select a table with cell references, using
the AFC East division as an example. In the VBE, insert a
new module and create a subroutine which starts by selecting the NFL
2017 worksheet:
Begin the subroutine as shown here.
If we know the range of cells that we want to copy, it's simple enough to use
the references of those cells. In this case, the range of cells is
A5 to D9:
We want to copy five rows and four columns.
We can reference these cells using the Range property, as we
have in previous lessons:
This style of cell referencing uses the first parameter of the
Range property, called Cell1.
After referencing the range of cells we can apply the Copy
method to it:
This instruction will copy the fixed range of cells we have referenced.
To paste the data into another worksheet we can either use the
PasteSpecial method or set the Destination parameter
of the Copy method. In the example below we use the
Destination parameter:
We set the
Destination to refer to cell A1 on the
AFC East worksheet.
After running
the subroutine we can check that the results appear on the correct worksheet:
You'll need to select the AFC East worksheet to see the results.
Using the Cell2 Parameter
For the next example we'll copy the NFC East table to the
correct worksheet. Create a new subroutine which starts by selecting the
NFL 2017 worksheet:
The start of the subroutine.
This time, the range of cells we want to copy is F5 to
I9:
We want to copy the
NFC East table.
Add an instruction
which begins referring to cell F5:
We're passing
F5 to the Cell1 parameter.
Next, enter a comma followed by the second cell reference:
We're passing
I9 to the Cell2 parameter.
You can then close the parentheses and apply the Copy method as
before:
This instruction will copy every cell from
F5 to I9.
When you want to reference a fixed range of cells, using the Cell2
parameter is perhaps a little awkward compared to using only the Cell1
parameter. Using the Cell2 parameter is key to the next
technique you'll learn however!
You can set the Destination parameter of the Copy
method to send the copied cells to the correct worksheet:
Set the
Destination to cell A1 on the
NFC East worksheet.
You can run the
subroutine and check that the results appear in the correct place:
Select the
NFC East worksheet to see the results.
Finding the Bottom Corner of a Table
Using cell references works well when you know the size of the table you want
to reference. When the table can vary in size, you can use the End
property to find its bottom corner. Create a new subroutine which starts
by selecting the NFL 2017 worksheet:
The now-familiar beginning to the subroutine.
In this example, we'll copy the AFC North table:
For this example we'll assume that we know the table begins in cell
A12 but that we don't know how many rows and columns it contains.
Add a new instruction which begins by referencing cell A12:
We'll assume that we know where the table starts.
Next, we'll use the End property to find the bottom row of the
table. We'll do this by setting the Cell2 parameter of
the Range property:
We set the
Cell2 parameter to reference the cell at the end of the list moving downwards from cell
A12.
To demonstrate what's happened so far, let's apply the Select
method so that we can see which range of cells we're currently referring to:
Make sure that you type two closing parentheses before applying the
Select method.
Run the subroutine and check the results in Excel:
You should find that cells
A12 to A16 are selected.
At this point we've selected from cell A12 to the bottom of the
list:
This diagram shows which cells the
Cell1 and Cell2 parameters are currently pointing to.
We now need to make the Cell2 parameter reference the cell in
the bottom right corner of the table:
The Cell2 parameter should refer to cell
D16.
We can do this by using a second End property to find the right
hand edge of the table. Start by positioning the cursor between the two
closing parentheses as shown below:
Click between the two closing parentheses.
Now apply another End property to the range returned by the
first End property:
This time use the
xlToRight direction.
Run the
subroutine to check that the correct block of cells is selected:
You should find this block of cells is selected.
Now you can replace the Select method with the Copy
method to copy the cells to the correct location:
The final subroutine should look like this.
Run the subroutine and check that the cells are copied to the correct sheet:
The cells should appear on the
AFC North worksheet.
A Quick Way to Select a Table
If you want to reference an entire table, you don't need to use the
End property to do so. To demonstrate the quick way to select a
table we'll copy the NFC North table:
We want to copy cells
F12 to I16.
Create
a new subroutine which begins by selecting the NFL 2017
worksheet and then refer to cell F12:
You need to refer to any cell which belongs to the table you want to reference. Usually, this will be the top left cell of the table.
You can now refer to the CurrentRegion property of the cell:
CurrentRegion is a property which returns a reference to the table to which the referenced cell belongs.
To demonstrate which range of cells you're referring to, apply the
Select method at the end of the instruction:
Apply the
Select method as shown here.
Run the subroutine and check which cells have been selected:
The code selects the entire table to which cell
F12 belongs.
You can now edit the
code so that it copies the cells rather than selecting them:
Copy the cells to the
NFC North worksheet.
Run the
subroutine and check the results in Excel:
You should find the copied data on the
NFC North worksheet.
