Databases Reference
In-Depth Information
ActivateWorkbook (wkbook)
Worksheets(wksht).Select
'Determine Range
RRange$ = Trim$(str$(rowptr)) & ":" & Trim$(str$(rowptr +
number - 1))
Rows(RRange$).Select
Selection.Insert Shift:=xlDown
rowptr = rowptr + number + 1
End Sub
Often, many reports must be prepared, and they will all share much of the same information.
In such an instance, it may be easier to copy a preexisting sheet, delete the items not needed, and
add any items that are required. The following subroutine will create a copy of any sheet to a
location in the Workbook specified by the user.
Sub CopyWorksheet(SourceBook, CopySheet, DestBook, PivotSheet,
beforepivot As Boolean, newsheetname)
'Make a Copy of (copysheet) contained within (sourcebook) to
destination Workbook (destbook)
'before sheet (beforesheet)
Select Case beforepivot
Case True
Workbooks(SourceBook).Worksheets(CopySheet).Copy
Before:=Workbooks(DestBook).Sheets(PivotSheet)
Case False
Workbooks(SourceBook).Worksheets(CopySheet).Copy
After:=Workbooks(DestBook).Sheets(PivotSheet)
End Select
ActiveSheet.Name = newsheetname
End Sub
Existing Worksheets can also be renamed at will utilizing the next subroutine.
Sub RenameSheet(wkbook, wksheet, newname)
'Renames the specified Worksheet in Workbook to (newname)
Workbooks(wkbook).Worksheets(wksheet).Name = newname
End Sub
When a user is working on a report, or when a macro is executing, it can be very useful to
provide the user with messages or instructions that appear on Excel's status bar. The next subroutine
will write any message desired to Excel's status bar.
Sub StatusBarMsg(ByVal Msg$)
'This will Display a Message to Excel's Status Bar
'Make Sure Status Bar Display is Turned on
If Application.DisplayStatusBar = False Then
Application.DisplayStatusBar = True
'Display String Passed in Msg$ Variable
Application.StatusBar = Msg$
End Sub
