The code below copies 2 adjacent rows and inserts them either above or
below the 2 current rows (2 rows = 1 record). However, I need to
clear certain parts of the 2 newly inserted rows:
--contents of cols. D and E (absolute),
--contents of cols. AP to the end of all consecutive cols. with the
text "Activity" in row 12 (the ending col. is relative since the
number of activities will increase). "Activity" is inserted into each
of these cols. as labels in row 12.
--and the contents of all consectutive cols. with dates 'mm/dd/yy'
typed in row 12 (the starting col. is relative since new 'Activity'
cols. will be inserted through time). The ending col. can just be IV.
I've been playing around with range().clear, but cannot figure out how
to clear these separate ranges on the 2 separate rows.
=================
'Great code from Bernie Deitrick
Sub InsertBlockAboveOrBelow()
Dim myCell As Range
Dim newFName As String
Dim newLName As String
newFName = InputBox("What is the new first name?")
newLName = InputBox("What is the new last name?")
If Cells(ActiveCell.Row, 1).Value = "" Then
Set myCell = Cells(ActiveCell.Row, 1).End(xlUp)
Else
Set myCell = Cells(ActiveCell.Row, 1)
End If
With myCell.Resize(2).EntireRow
If MsgBox("Above = ""Yes"", Below = ""No""", vbYesNo) = vbYes Then
.Copy
.Insert
myCell.Offset(-2).Value = newFName
myCell.Offset(-2,1).Value = newLName
Else
.Copy
.Offset(4).Insert
myCell.Offset(2).Value = newFName
myCell.Offset(2,1).Value = newLName
End If
End With
Application.CutCopyMode = False
End Sub
Thanks,
Eric
|