Drag and drop existing data into cells, BUT ignoring proteced cells and pasting into the next available row

[Simonr900]

Hi,
I'm using excel to organise a production schedule and want to be able to drag and drop certain 'jobs' within a sheet, but whilst doing so I'd like the data that's being dragged and dropped to ignore protected cells and move on to the next row - the protected cells are overtime and will be locked/unlocked depending on whether there's overtime available, or not...

Hopefully this makes sense..?

 

[Joe4]

Welcome to the Board!

I would be more apt to create a little VBA procedure that can be run that prompts them for what row to copy to what row.
You can then control exactly which columns are copied in your code.

If that is an acceptable solution, please provide us with the details we need to come up with this VBA code for you (i.e. which columns to include in the copy).

 

[Simonr900]

Welcome to the Board!

I would be more apt to create a little VBA procedure that can be run that prompts them for what row to copy to what row.
You can then control exactly which columns are copied in your code.

If that is an acceptable solution, please provide us with the details we need to come up with this VBA code for you (i.e. which columns to include in the copy).

Good morning,

Thanks for your response.

Please see image attached of what i'm trying to achieve, I'm not sure if your proposal will work?

Thanks,
Simon

 

[Joe4]

Hmmm... I see your dilemma.
I don't think that you are going to be able to do that with drag & drop.
I think it will require some type of VBA solution.

Here is an option that may work for you.
Here are the steps:
1. Select the range you want to move (in looking at your example, I am assuming it will only be one column wide)
2. Run the code
3. Enter the first cell you want to move it to at the prompt

It will then move the data like you want, skipping the protected rows.

Sub MyMoveMacro()

    Dim chk
    Dim cols As Long
    Dim add As String
    Dim rng As Range
    Dim cell As Range
    Dim fnd As Boolean

'   Confirm that they have selected the range they wish to move
    chk = MsgBox("Have you already selected the range you wish to move?", vbYesNo, "CONFIRM SELECTION")
    If chk <> vbYes Then
        MsgBox "Select the range you wish to move and try again", vbOKOnly, "TRY AGAIN!"
        Exit Sub
    End If

'   Check the number of columns in the selection
    cols = Selection.Columns.Count
    If cols > 1 Then
        MsgBox "You can only select a one-column range to move", vbOKOnly, "TRY AGAIN!"
        Exit Sub
    End If
    
'   Prompt them to enter the address of the cell they want to move the data to
    add = InputBox("Please enter the address of the first cell to move the data to.", "WHERE TO MOVE TO?")
    On Error GoTo err_chk
    Set rng = Range(add)
    On Error GoTo 0
    
'   Loop through all cells in selection and move to new area
    For Each cell In Selection
        fnd = False
'       Find first unlocked cell and copy
        Do
'           Copy cell to new range if cell is not locked
            If rng.Locked = False Then
                rng.Value = cell.Value
                fnd = True
            End If
'           Move down one row
            Set rng = rng.Offset(1, 0)
'           Exit if cell written
            If fnd = True Then Exit Do
        Loop
    Next cell
                
'   Clear original range
    Selection.Clear
    
'   Exit sub before error handler
    Exit Sub
    
    
'Error handling code
err_chk:
    If Err.Number = 1004 Then
        MsgBox "You have not entered a valid cell address.", vbOKOnly, "TRY AGAIN!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

Will that work for you?

 

[Simonr900]

Hi,

This doesn't really work how I'd like, unfortunately.

I would like to stick with the 'drag and drop', if possible - this will work if not.

It will be 5 columns I'd like to move at a time - can the macro be amended to suit?

Thanks

 

[Joe4]

This doesn't really work how I'd like, unfortunately.

I would like to stick with the 'drag and drop', if possible - this will work if not.

As I said, I don't think it is possible without VBA. You are not going to be able to use the native "drag-and-drop" functionality with the design of this worksheet.

It will be 5 columns I'd like to move at a time - can the macro be amended to suit?

If you do not like my solution, is there any point in amending it?
I don't want to waste a lot of time on a solution you do not intend to use (it isn't exactly trivial to come up with).

If you do want to pursue an amendment, is it safe to assume that the lock/protection is consistent across the entire row (i.e. you do not have rows where some cells are locked and others aren't)?

 

[Simonr900]

As I said, I don't think it is possible without VBA. You are not going to be able to use the native "drag-and-drop" functionality with the design of this worksheet.


If you do not like my solution, is there any point in amending it?
I don't want to waste a lot of time on a solution you do not intend to use (it isn't exactly trivial to come up with).

If you do want to pursue an amendment, is it safe to assume that the lock/protection is consistent across the entire row (i.e. you do not have rows where some cells are locked and others aren't)?

Hi Joe,

Your solution will work much better than what I've got so far, if you don't mind amending to suit the 5 columns it'd be appreciated.

Thanks,
Simon

 

[Joe4]

OK, try this version and see if it works:

Sub MyMoveMacro()

    Dim chk
    Dim cols As Long
    Dim add As String
    Dim rng As Range
    Dim cell As Range
    Dim fnd As Boolean

'   Confirm that they have selected the range they wish to move
    chk = MsgBox("Have you already selected the range you wish to move?", vbYesNo, "CONFIRM SELECTION")
    If chk <> vbYes Then
        MsgBox "Select the range you wish to move and try again", vbOKOnly, "TRY AGAIN!"
        Exit Sub
    End If

'   Count the number of columns in the selection
    cols = Selection.Columns.Count
    
'   Prompt them to enter the address of the cell they want to move the data to
    add = InputBox("Please enter the address of the first cell to move the data to.", "WHERE TO MOVE TO?")
    On Error GoTo err_chk
    Set rng = Range(add)
    On Error GoTo 0
    
'   Loop through all cells in first column of selection and move to new area
    For Each cell In Selection.Columns(1).Cells
        fnd = False
'       Find first unlocked cell and copy
        Do
'           Copy cell to new range if cell is not locked
            If rng.Locked = False Then
                cell.Resize(1, cols).Copy rng
                fnd = True
            End If
'           Move down one row
            Set rng = rng.Offset(1, 0)
'           Exit if cell written
            If fnd = True Then Exit Do
        Loop
    Next cell
                
'   Clear original range
    Selection.ClearContents
    
'   Exit sub before error handler
    Exit Sub
    
    
'Error handling code
err_chk:
    If Err.Number = 1004 Then
        MsgBox "You have not entered a valid cell address.", vbOKOnly, "TRY AGAIN!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[Simonr900]

That works perfectly - thanks a lot for your help!!

 

[Joe4]

You are welcome.
Glad I was able to help.

I am not sure how familar you are with VBA (so forgive me if I am telling you something here that you already know), but there are various different ways to run VBA code.
You can run it from the Macros menu, you can assign it to a button you put on your worksheet, or you can use a keyboard shortcut.
So you can choose whichever method makes the most sense for your project, and easiest for your users to use.