Excel VBA(2) Workbooks and Worksheets

The Workbooks Collection

Sub ActivateWorkbook1()
	Dim sFullName As String
	Dim sFileName As String
	Dim wkb As Workbook
	sFullName = “C:\Data\SalesData1.xlsx”
	sFileName = sGetFileName(sFullName)
	If bIsWorkbookOpen(sFileName) Then
		Set wkb = Workbooks(sFileName)
		wkb.Activate
	Else
		Set wkb = Workbooks.Open(FileName:=sFullName)
	End If
End Sub

The Sheets Collection

Worksheets

Better to avoid using index to specify a sheet, like Worksheets(2)

Copy and Move

eg:

Sub NewMonth()
	'Copy the first worksheet in the active workbook
	'to create a new monthly sheet with name of format “mmm yyyy”.
	'The first worksheet must have a name that is in a recognizable
	'date format.
	Dim wks As Worksheet
	Dim dteFirstDate As Date
	Dim iFirstMonth As Integer
	Dim iFirstYear As Integer
	Dim iCount As Integer
	'Initialize counter to number of worksheets
	iCount = Worksheets.Count
	'Copy first worksheet after last worksheet and increase counter
	Worksheets(1).Copy After:=Worksheets(iCount)
	iCount = iCount + 1
	'Assign last worksheet to wks
	Set wks = Worksheets(iCount)
	'Calculate date from first worksheet name
	dteFirstDate = DateValue(Worksheets(1).Name)
	'Extract month and year components
	iFirstMonth = Month(dteFirstDate)
	iFirstYear = Year(dteFirstDate)
	'Compute and assign new worksheet name
	wks.Name = Format(DateSerial(iFirstYear, iFirstMonth + iCount - 1, 1), “mmm yyyy”)
	'Clear data cells in wks, avoiding error if there is no data
	wks.Cells.SpecialCells(xlCellTypeConstants, 1).ClearContents
End Sub

Important:

Month(date), Year(date), Format(date, style)

Grouping Worksheets

'eg1
Worksheets(Array(1, 3, 5)).Select
Worksheets(3).Activate

'eg2
Sub GroupSheets()
	Dim asNames(1 To 3) As String
	Dim i As Integer
	
	asNames(1) = "Jan 2008"
	asNames(2) = "Feb 2008"
	asNames(3) = "May 2008"

	Worksheets(asNames(1)).Select

	For i = 2 To 3
		Worksheets(asNames(i)).Select Replace := False
	Next i
End Sub

'eg3
Sub FormatGroup()
	Dim shts As Sheets
	Dim wks As Worksheet

	Set shts = Worksheets(Array(1, 3, 5))
	
	For Each wks In shts
		wks.Range("A1").Value = 100
		wks.Range("A1").Font.Bold = True
	Next wks
End Sub

 

The Window Object

Sub FormatSelectedGroup()
	Dim sht As Object
	Dim sRangeAddress As String
	sRangeAddress = Selection.Address
	For Each sht In ActiveWindow.SelectedSheets
	If TypeName(sht) = “Worksheet” Then
	sht.Range(sRangeAddress).Font.Bold = True
	End If
	Next sht
End Sub