Can you delRange.Cut as well as delRange.Delete?

[lrp20]

Hi Everyone,

I hope this will make sense but rather than find, select and delete a row of data in my workbook I wish to cut that data and move it to a secondary tab instead.

To explain a little more…

In my workbook I have a ‘Master Database’ tab which contains only the most recently entered info for each project (i.e. is the ‘live’/current status for each project). Each project has a unique ID.

Users can update their projects info as time progresses, in the ‘Update’ tab. Once their project info is tweaked /updated they click ‘Submit’. This starts a loop to search for the Project ID in the ‘Master’ tab.

Originally the project ID would be found and its encompassing row would be selected and deleted. Then the updated info would be pasted on to the next available row in this ‘Master’ tab.

I now need to change this however. Instead of finding the ID, selecting and then deleting the requisite row I have to find the ID, select the row and then cut and paste this row to another tab - entitled ‘History’ – (and then finish the job by pasting the newly updated project info on to the next available row on the ‘Master’ tab).

My code is below, and works ~50% of the time. The remaining 50% gives this error message and I can’t work out why the problem is intermittent.

“Run-time error ‘1004’:
PasteSpecial method of Range class failed.”

The line of code pointed to by debugging =

History.Range("A500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Any/all help gratefully received! I’m a novice who’s compiled this from lots of forum reading so aware my code may not be the smartest.

Sub Submit_Update()
 
'Checks ID against *Master* (STEP 1)
'Copy *Update_Form* data to *Master* (STEP 2)
'Clear *Update_Form* for next user (STEP 3)
      
    Dim Update_Form As Worksheet
    Dim MASTER As Worksheet
    Dim History As Worksheet
    Dim Data_Formatting As Worksheet
    Dim i As Long
    Dim delRange As Range
    Dim Response As VbMsgBoxResult
             
    Set MASTER = Worksheets("MASTER DATABASE")
    Set History = Worksheets("MASTER HISTORY")
    Set Update_Form = Worksheets("Update Efficiency")
    Set Data_Formatting = Worksheets("DATA FORMATTING")
 
 
'====================================================================================
   
'STEP 1A - Confirms users intent to overwrite data
                       
            Response = MsgBox("Unique OC ID already exists in Master Database. Overwrite with this updated data?", vbQuestion + vbYesNo)
   
'====================================================================================
       
'STEP 1B - If Response is Yes Then find and cut existing row in *Master*
       
        If Response = vbYes Then
       
        With MASTER
       
            '~~> Loop through relevant rows
            For i = 3 To 500
           
                '~~> Check if project ID in *Master* worksheet equals to B2 in *Update_Form* worksheet
            If (Trim(.Cells(i, 1).Value)) = Update_Form.Range("B2").Value Then
                   
                    '~~> Store the Range to delete later
                    If delRange Is Nothing Then
                        Set delRange = .Rows(i)
                   
                    Else
                        Set delRange = Union(delRange, .Rows(i))
                   
                    End If
                End If
            Next i
        End With
 
        Application.CutCopyMode = True
 
       
        If Not delRange Is Nothing Then delRange.Copy
        History.Range("A500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
       
 
'====================================================================================
       
'STEP 1C - If Response = vbNo Then just do nothing and close MsgBox
 
        ElseIf Response = vbNo Then Exit Sub
 
End If
 
 
'====================================================================================
 
'STEP 2A - Copy multiple ranges of data from *Update_Form* and paste as single range in *Data_Formatting*
   
‘Code from this point onwards removed to avoid clogging up space on forum help request
 
'====================================================================================
       
'STEP 2B - Now, copy this single range from *Data_Formatting* and transpose paste into next available row in *Master*
'Table should auto expand
     
       
'====================================================================================
       
'STEP 3 - Clear *Update_Form* of data
 
       
End Sub

 

[svendiamond]

How many workbooks do you have open at the same time? Are you switching between them while executing the code?

Try putting "ThisWorkbook" in front of your worksheet lines so that Excel knows which workbook has the "History" sheet in it.

With ThisWorkbook
    Set MASTER = .Worksheets("MASTER DATABASE")
    Set History = .Worksheets("MASTER HISTORY")
    Set Update_Form = .Worksheets("Update Efficiency")
    Set Data_Formatting = .Worksheets("DATA FORMATTING")
End With

 

[lrp20]

Hi there thanks for replying. I should have stated - all the worksheets are in one workbook so there's no switching between files.

 

[svendiamond]

Okay. I'm just trying to think of why it would have an issue finding that range to paste it in. Are you sure your data doesn't ever go past row 500 on the history sheet? Have you noticed anything different between times when your macro works vs times when it gives you the error?

 

[StephenCrump]

What happens when delRange is Nothing?

You skip the Copy, but you still try to Paste, hence the error:

Application.CutCopyMode = True
If Not delRange Is Nothing Then delRange.Copy
History.Range("A500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

 

[svendiamond]

Ding ding ding!

 

[lrp20]

Hi Stephen, many thanks for your reply. I'm afraid the fullness of my ineptitude / novice status is on show here and I'm probably not understanding how the code is meant to operate.

There is a 'If delRange is Nothing' line 10 line or so above the line that's causing the error, and there's a copy command above the line that's causing the error. I'm probably being frustratingly slow and I apologise - please could you expand on your answer at all? I'm very grateful for your help.

 

[svendiamond]

In your original post, you said this line was causing the error:

The line of code pointed to by debugging =


History.Range("A500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Stephen noticed that the reason this could happen is if delRange is Nothing, you don't do the "copy" command but you still try run this "pastespecial" command. So you need to change those lines to:

If Not delRange Is Nothing Then
     delRange.Copy
     History.Range("A500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

 

[lrp20]

Hi SvenDiamond, I'm afraid I've still not twigged . The code you've said to insert is already in the macro?

 

[svendiamond]

There is a difference between

If delRange Is Nothing Then delRange.Copy

And

If delRange Is Nothing Then
delRange.Copy