Databases Reference
In-Depth Information
trowptr = trowptr + 1 'Only Increment this pointer with
Copy or Move Event
Select Case CopyMoveButton.Caption
Case "Move"
Debug.Print "Move to Sheet: "; CopyToSheet
Call
MoveRow
(frowptr,
CopyFromSheet, CopyFromBook,
trowptr, CopyToSheet, CopyToBook, True, True)
frowptr = frowptr - 1 'Decrement this pointer as
row was deleted after
move!
Case "Copy"
Debug.Print "Copy to Sheet: "; CopyToSheet
Call
CopyRow
(row,
CopyFromSheet, CopyFromBook,
trowptr, CopyToSheet, CopyToBook, True)
End Select
End If
Next row
End
End Sub
This subroutine has the usual error-checking mechanisms to make sure that the user selected
the items necessary for the subroutine to function. It is the looping mechanism that accomplishes
the copying and moving of the data; this is of particular interest. Notice that two pointers are
utilized to designate the following: the row that the information is to be copied from, and the row
that the information is to be moved to.
The
frowptr
(from row pointer) variable is utilized to point to the row
from
which the data
will be copied or moved. The
trowptr
(to row pointer) variable is utilized
to
point to the row
where the data will be placed after being copied or moved.
Two pointers are required for the following reasons. Information will not be copied or moved
unless it meets the specified criteria. Therefore, the row in which information is copied from on
one sheet will not necessarily be the row it is pasted to on the destination sheet. Notice that the
trowptr
is only incremented immediately prior to a copy or move action on the part of the
subroutine. The
trowptr
(to row pointer) will attain a maximum value equal to the number of rows
copied from the source Worksheet.
A further complication exists when trying to move the data. Because the information is first
copied on the source sheet and then deleted on it (shifting the rows up), the row from which
information should be copied or moved from will not necessarily be the same as the loop index.
Notice that the
frowptr
(from row pointer) is always incremented at the start of each loop. In
the case of simply copying the data, if it meets a certain condition, the
frowptr
value will match
that of the loop index. In the case of moving the data, however, each time a row of data is deleted
and removed from the source sheet, the
frowptr
value must be decremented by one. The reason
for this is that, when a row of data is deleted, the next row of data
shifts upward
to fill the void
left when the row was deleted. As soon as this occurs, the next row to be analyzed or possibly
moved is now one less than the loop counter. After this occurs twice, the next row to be analyzed
or possibly moved is now two less than the loop counter, and so on. The
frowptr
(from row
pointer) is in a constant state of self adjustment, adjusting its value
downward
to compensate for
data
shifting upward
when a row is moved from the source Worksheet to the destination Worksheet.
Two subroutines are actually responsible for moving or copying a row of data. The
CopyRow
subroutine will copy a row of information from any Worksheet in any Workbook to any Worksheet
in any Workbook. Notice the Boolean passed parameter
PreserveFormatting
, which allows
