D
David Ryan
Hi Folks
I with the help of Patrick Molloy, Kevin Smith and Joel from these groups
(thanks guys!) created a spreadsheet with a macro to move data from one row
to another. The sheet is locked so has to unlock etc prior to moving. Works
great however i need it to not move that data if the destination cells has
data already entered so should prompt for another destination. See attached
code.
Also to push the friendship
I would like the ability to move the data based in the data in the first
column e.g.
row 6 column A may have "1C" entered and row 8 column A May have 1B. Rather
than entering the row 6 in the input box from field the user enters what is
in cell 6A ie 1C etc
Again thanks in advance
Option Explicit
Sub MoveData()
Dim FROMrow As Long
Dim TOrow As Long
Dim c As Range
'To unprotect:
ActiveSheet.Unprotect Password:="pcc"
'To protect:
'ActiveSheet.Protect Password:="pcc"
On Error GoTo Canceled
FROMrow = InputBox("FROM row: ....", "MOVE FROM ")
TOrow = InputBox("TO row:...", "MOVE TO ...")
Cells(FROMrow + 5, "B").Resize(, 24).Copy
Cells(TOrow + 5, "B").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Cells(FROMrow + 5, "B").Resize(, 24).ClearContents
'To protect:
ActiveSheet.Protect Password:="pcc"
ActiveSheet.Unprotect Password:="pcc"
Range("H6:H45").Select
For Each c In Selection
If c.Value = "" And _
c.Locked = True Then
c.Locked = False
End If
Next
Range("H1").Select
ActiveSheet.Protect Password:="pcc"
Canceled:
End Sub
I with the help of Patrick Molloy, Kevin Smith and Joel from these groups
(thanks guys!) created a spreadsheet with a macro to move data from one row
to another. The sheet is locked so has to unlock etc prior to moving. Works
great however i need it to not move that data if the destination cells has
data already entered so should prompt for another destination. See attached
code.
Also to push the friendship
I would like the ability to move the data based in the data in the first
column e.g.
row 6 column A may have "1C" entered and row 8 column A May have 1B. Rather
than entering the row 6 in the input box from field the user enters what is
in cell 6A ie 1C etc
Again thanks in advance
Option Explicit
Sub MoveData()
Dim FROMrow As Long
Dim TOrow As Long
Dim c As Range
'To unprotect:
ActiveSheet.Unprotect Password:="pcc"
'To protect:
'ActiveSheet.Protect Password:="pcc"
On Error GoTo Canceled
FROMrow = InputBox("FROM row: ....", "MOVE FROM ")
TOrow = InputBox("TO row:...", "MOVE TO ...")
Cells(FROMrow + 5, "B").Resize(, 24).Copy
Cells(TOrow + 5, "B").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Cells(FROMrow + 5, "B").Resize(, 24).ClearContents
'To protect:
ActiveSheet.Protect Password:="pcc"
ActiveSheet.Unprotect Password:="pcc"
Range("H6:H45").Select
For Each c In Selection
If c.Value = "" And _
c.Locked = True Then
c.Locked = False
End If
Next
Range("H1").Select
ActiveSheet.Protect Password:="pcc"
Canceled:
End Sub