This template makes it easy for you to create a weekly school schedule. The template has hours divided into 10 minute intervals. You can easily change the conditional formatting color.

How it´s done?

• Conditional Formatting
• Array formulas
• Dynamic named ranges
• No vba

Download template excel 2007 *.xlsx

school schedule.xlsx

In this blog post I will demonstrate some vba copying techniques.

Example 1,

This example code copies cell range A1:B2 to A5:B6 in the active sheet. You can make the code even shorter: Range("A1:B2").Copy Range("A5").

Example 2,

The current region is a cell range surrounded by blank rows and columns. This is equivalent to Ctrl + a.

This works very well if the cell range doesn´t have blank rows or columns.

Example 3,

Cell range A1:B6 has blank cells in row 4.

Example 4,

This cell range (A1:B6) has a blank row (4) and a blank cell (A6).

Download excel 2007 *.xlsm file

copying techniques.xlsm

Functions:

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

VBA:

Worksheet Range Property

Worksheet Rows Property

Range.CurrentRegion Property

End Property 

Michael asks:

I need to identify the duplicates based on the Columns D:H and put in Column C a small “d” + a running number for the duplicates which are duplicated the same.
If no duplicates only to put in a “d0” for instance.

Answer

Array formula in cell B3:

How to create an array formula

1. Double click cell B3
2. Copy/Paste above formula
3. Press and hold Ctrl + Shift
4. Press Enter

How to copy array formula

1. Select cell B3
2. Copy (Ctrl + c)
3. Select cell range B4:B11
4. Paste (Ctrl + v)

Download excel 2007 *.xlsx file

Group duplicates.xlsx

In this post I am going to demonstrate how to create a new sheet in the current workbook and list all open workbooks and their sheets using visual basic for applications.

Example,

Macro code

Sub ListWorkbooks()
Dim wb As Workbook
Dim ws As Worksheet
Dim i, j As Single
Set ws = Sheets.Add
For j = 1 To Workbooks.Count
    Range("A1").Cells(j, 1) = Workbooks(j).Name
    For i = 1 To Workbooks(j).Sheets.Count
            Range("A1").Cells(j, i + 1) = Workbooks(j).Sheets(i).Name
    Next i
Next j
End Sub

How to create macro

1. Open VB Editor, press Alt+F11.
2. Right click on your workbook in project explorer.
3. Click "Insert" and "Module".
4. Click in code module window.
5. Paste macro code.
6. Exit to excel.

How to run macro

1. Go to "Developer" tab on the ribbon
2. Click "Macros" button
3. Select "ListWorkbooks" macro
4. Click "Run" button

Download example file

List all open workbooks and corresponding sheets.xlsm

In this small tutorial I am going to show you how to create basic data entry with a small amount of vba code.

Cell B3 and C3 are input cells. When you click button "Add", the data in cell B3 and C3 are copied to the first empty row in the list.

The last step in the code removes the values in cell B3 and C3.

Create macro

1. Open vb editor, press Alt+F11
2. Insert a new module

Step 1 - Find last nonempty cell in column B

We can find the last nonempty cell in column B with this code:

We want the first empty cell so we add one to the code.

This line of code saves the value in the variable Lrow. In the example given, the code saves the value 8 in Lrow.

Step 2 - Copy values

Let´s use the value in Lrow and copy values from cell B3:C3 to the first empty row.

Worksheets("Sheet1").Range("B" & Lrow & ":C" & Lrow) = Worksheets("Sheet1").Range("B3:C3").Value

becomes

Worksheets("Sheet1").Range("B8:C8") = Worksheets("Sheet1").Range("B3:C3").Value

Step 3 - Remove values

Worksheets("Sheet1").Range("B3:C3").Value = ""

Step 4 - Optimize code

Worksheets("Sheet1") is repeated many times. Let me show you how to optimize the code.

Sub AddText()
Dim Lrow As Single
With Worksheets("Sheet1")
   Lrow = .Range("B" & Rows.Count).End(xlUp).Row + 1
   .Range("B" & Lrow & ":C" & Lrow) = .Range("B3:C3").Value
   .Range("B3:C3").Value = ""
End With
End Sub

Step 5 - Create button

1. Go to developer tab
2. Click "Insert" button
3. Click "Button (Form Control)"
4. Create a button
5. Right click on your new button
6. Assign a macro
7. Select "AddText" macro

Final note:

There is a data entry form in excel but it is not on the ribbon.

Download excel file *.xlsm

Basic data entry.xlsm