What you learn Referencing Ranges, Worksheets & Workbooks
Link Topic Covered What you Learn
Referencing Cells Different methods to ▪ Range ()
reference ranges ▪ Cells property of the range object
▪ Offset
▪ Defined Names
▪ Reference entire rows
▪ Reference entire columns
▪ Autofit all columns
Example_P_M Example of Range Property ▪ Text versus Value range property
& Method ▪ Delete method
▪ Select method
Range_Reference How to find the end (or 1. Use End Property (from top / bottom for rows or left / right
beginning) of ranges for columns)
2. Use CurrentRegion Property
3. Use SpecialCells Property
4. Use UsedRange Property of the Worksheet object
Copy_Resize Copy & Resize Variably ▪ Copy Paste method
Sized Ranges ▪ PasteSpecial method
▪ Copy different sized ranges
▪ Resize ranges (for example exclude header from copy)
Worksheet How to Best Reference ▪ Use sheet code names to reference the right sheet
Worksheets ▪ Use sheet names to reference the right sheet
Workbook How to Best Reference ▪ Use workbook names to reference the right workbook
Workbooks ▪ Use ThisWorkbook to reference the workbook the macro is in
Activity Activity: Create a hard- See details in the Activity Test Tab
coded Copy of this
Workbook
On this sheet we will practice
the different methods to refer
to write to cells.
will practice
hods to refer
Example of Range Property & Method
In Excel 10,200
Copied with .Value
Copied with .Text
How to find the end (or beginning) of ranges
Business Actual Budget
Company Unit Main Unit Red Flag Revenue Revenue
Entity A BU_1 VC_A Y 10,200 10,404
Entity B BU_1 VC_A N 12,240 12,485 Find Last row here:
Entity C BU_1 VC_A N 14,688 14,982 Next empty row #:
Entity D BU_1 VC_A N 19,776 17,978 Last column #:
Entity E BU_2 VC_A Y 10,300 10,506 Address of current region:
Entity F BU_2 VC_A Y 12,360 12,607 # of rows in data set:
Entity G BU_2 VC_A Y 14,832 15,129 last used row on sheet:
Entity H BU_2 VC_A Y 17,798 18,154 Last used row method 2:
Entity I BU_3 VC_B Y 10,400 10,608
Entity J BU_3 VC_B Y 12,730 12,730
Entity K BU_3 VC_B Y 14,976 15,276 Note: Be careful with End property. If
there are blank cells in the middle of
Entity L BU_4 VC_B Y 20,000 20,400 your data set, end will not provide the
Entity M BU_4 VC_B Y 24,000 24,480 required result. It would be safer to use
Entity N BU_4 VC_B Y 28,800 29,376 CurrentRegion or UsedRange property of
the worksheet.
Entity O BU_4 VC_B Y 34,560 35,251
Entity P BU_5 VC_B Y 41,472 42,301
Entity Q BU_6 VC_C Y 49,766 50,762
Entity R BU_6 VC_C Y 10,500 10,710
Entity S BU_6 VC_C Y 23,731 21,574
Entity T BU_6 VC_C Y 25,381 25,888
Entity U BU_7 VC_C Y 30,457 31,066
Entity V BU_7 VC_C Y 10,200 10,404
Entity W BU_7 VC_C Y 12,240 12,485
Entity X BU_7 VC_C Y 14,688 14,982
Entity Y BU_8 VC_C Y 10,100 10,302
Entity Z BU_8 VC_C Y 10,300 10,506
End property. If
n the middle of
l not provide the
uld be safer to use
dRange property of
Copy & Resize Variably Sized Ranges
Source Data Destination with copy method
Business Actual Budget
Company Unit Revenue Revenue Variance
Entity A BU_1 10,200 10,404 -2%
Entity B BU_1 12,240 12,485 -2%
Entity C BU_1 14,688 14,982 -2%
Entity D BU_1 19,776 17,978 10%
Entity E BU_2 10,300 10,506 -2%
Entity F BU_2 12,360 12,607 -2%
Paste here without header Destination with paste special method
This lecture shows you the
different methods you can copy
and paste ranges.
ste special method
How to Best Reference Worksheets
In this lecture you learn various
methods to reference
worksheets.
How to Best Reference Workbooks
For the macro to work, make sure you create a new file called "DestBook.xlsx"
Update the path to this workbook in the VBA code.
In Section "Working with Files, Folders & Text Files", I show you how you can allow the user to select a specific workbook.
elect a specific workbook.
Activity: Create a hard-coded Copy of this Workbook
Use the knowledge you've gained until now to create a macro that saves a hard-coded copy of this workbook.
The copy, should have no formulas - We just want to have a back up of the file without any links or formulas.
The name of the new workbook should be "HC_" followed by the name of this workbook.
The steps for the macro would be:
1 Save the current workbook
2 Select all sheets, copy and paste special all cells in the sheets
3 Save the workbook with SaveAs (be careful you don't just SAVE, but SAVEAS)
4 Save it in the same directory as this workbook. Call the new workbook "HC_" followed by the name of th
Bonus: Add today's date in the form of "yymmdd" after "HC_" so HC_yymmdd_S4_ReferencingRanges"
Bonus #2: Save the copy without macros (i.e. the copied version should be a normal xlsx file)
Answer is in the project video and included in the complete version of this workbook in Module "ProjectActivity"
his workbook.
or formulas.
_" followed by the name of this book.
_ReferencingRanges"
in Module "ProjectActivity"
