Ok George, I think I've got it.
Martin Rice was right that you need a macro that unlocks and re-locks
the sheet.
I have written 2 new macros: One for copying and pasting within the
same worksheet, and the other for copying from an external worksheet to
the current sheet.
It occurred to me that there is a danger of accidentally pasting over a
row of good data, therefore I have included the following protection:
Column IV can be unlocked.
On each row of good data, column IV should contain the letter "P"
(uppercase). The macros will not paste onto a row that contains this
flag in column IV. If the user needs to override this feature, he or
she can do so by clearing the 'P' on that row.
If you don't want to trust the users, you can leave column IV locked so
that the users have to call you to unprotect the worksheet and clear the
P.
Because the code includes the password to unprotect the sheet, you
should paste the code into a button from the Control toolbox toolbar,
not from the Forms toolbar. When the sheet is protected it will not be
possible for users to right click the button and see the code.
After creating the buttons, click on each one's properties and change
the names to CopyRow and CopyExternalRow.
Option Explicit
Private Sub CopyExternalRow_Click()
ActiveSheet.Protect Password:="captain"
On Error GoTo errhandle
Dim iSourceRow As Integer
Dim iDestRow As Integer
Dim sDestRow As String
Dim sSourceRow As String
Dim sSourceBook As String
Dim sSourceSheet As String
Dim sDestBook As String
sDestBook = ThisWorkbook.Name
sSourceBook = Application.InputBox(Prompt:="Enter Workbook to copy
from" & Chr$(13) & "do not include file extension (e.g. .xls)",
Title:="SourceBook", Type:=2) & ".xls"
sSourceSheet = Application.InputBox(Prompt:="Enter Sheet to copy
from", Title:="SourceSheet", Type:=2)
iSourceRow = Application.InputBox(Prompt:="Enter Row to copy from",
Title:="SourceRow", Type:=1)
sSourceRow = "$" & iSourceRow & ":$" & iSourceRow
iDestRow = Application.InputBox(Prompt:="Enter Destination Row",
Title:="DestinationRow", Type:=1)
sDestRow = "$" & iDestRow & ":$" & iDestRow
'To prevent accidentally overwriting existing data, column IV of
the worksheet should be unprotected
'and you should enter the letter P in column IV of each row that
you want to protect. The macro will
'automatically enter a 'P' in column IV of your destination row.
If Range("IV" & iDestRow).Value = "P" Then
MsgBox "Row " & iDestRow & " is marked as protected." & Chr$(13) &
"Clear cell IV" & iDestRow & " to overwrite contents of row " &
iDestRow
End
Else
End If
ActiveSheet.Unprotect Password:="captain"
Windows(sSourceBook).Activate
ActiveWorkbook.Sheets(sSourceSheet).Range(sSourceRow).Copy
Windows(sDestBook).Activate
Range(sDestRow).PasteSpecial xlPasteAll
Range("IV" & iDestRow).Value = "P"
errhandle:
ActiveSheet.Protect Password:="captain"
End Sub
Private Sub CopyRow_Click()
ActiveSheet.Protect Password:="captain"
On Error GoTo errhandle
Dim iDestRow As Integer
Dim sDestRow As String
Dim sSourceRow As String
sSourceRow = "$" & ActiveCell.Row & ":$" & ActiveCell.Row
iDestRow = Application.InputBox(Prompt:="Enter Destination Row",
Title:="DestinationRow", Type:=1)
'To prevent accidentally overwriting existing data, column IV of
the worksheet should be unprotected
'and you should enter the letter P in column IV of each row that
you want to protect. The macro will
'automatically enter a 'P' in column IV of your destination row.
If Range("IV" & iDestRow).Value = "P" Then
MsgBox "Row " & iDestRow & " is marked as protected." & Chr$(13) &
"Clear cell IV" & iDestRow & " to overwrite contents of row " &
iDestRow
End
Else
End If
sDestRow = "$" & iDestRow & ":$" & iDestRow
ActiveSheet.Unprotect Password:="captain"
Range(sSourceRow).Copy
Range(sDestRow).PasteSpecial xlPasteAll
Range("IV" & iDestRow).Value = "P"
errhandle:
ActiveSheet.Protect Password:="captain"
End Sub