Databases Reference
In-Depth Information
Sub CopySection(ByVal frmwkbook, ByVal frmwksht, ByVal r1,
ByVal c1, ByVal r2, ByVal c2, ByVal towkbook, ByVal towksht,
ByVal row, ByVal col)
'Copies the "Chunk" Bounded by r1,c1,r2,c2 in frmwkbook to
towkbook starting at row,col
'Select "from" Workbook and Worksheet
ActivateWorkbook (frmwkbook)
Worksheets(frmwksht).Select
Range(Cells(r1, c1), Cells(r2, c2)).Select
Selection.Copy
'Select "to" Workbook and Worksheet
ActivateWorkbook (towkbook)
Worksheets(towksht).Select
Range(Cells(row, col), Cells(row, col)).Select
ActiveSheet.Paste
End Sub
One nice aspect of this subroutine is that it allows the user to specify what Workbook and
Worksheet the information will be copied
from
as well as what Workbook and Worksheet the
information will be copied
to
. This provides the ultimate in flexibility. Something else worth keeping
in mind is that if a range is copied from one Worksheet, and the user wishes to specify a range on
another Worksheet to copy that range to, the sizes of the ranges must be identical, or the process
will fail. This subroutine avoids having the user check if the ranges are identical by simply specifying
a starting position for the copying to begin. The selected range will be copied to the Worksheet
beginning at the starting point, and extending however many rows and columns from the starting
point that the original copied range encompassed.
Often, Worksheets will have a header, that is, a series of rows at the top of the Worksheet that
contain information relating to all the data contained in the Worksheet. It is often advantageous to
copy header information when constructing a new Worksheet. The following subroutine does just that.
Sub CopyHeader(ByVal frmwkbook, ByVal frmwksht, ByVal nrows,
ByVal towkbook, ByVal towksht)
'Copies a Header of n rows in frmwkbook to towkbook starting
at top of Worksheet
'Select "from" Workbook and Worksheet
Dim RRange$
ActivateWorkbook (frmwkbook)
Worksheets(frmwksht).Select
RRange$ = "1:" & Trim$(Str$(nrows))
Rows(RRange$).Select
Selection.Copy
'Select "to" Workbook and Worksheet
ActivateWorkbook (towkbook)
Worksheets(towksht).Select
Range(Cells(1, 1), Cells(1, 1)).Select
ActiveSheet.Paste
End Sub
