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]

Try stepping through your code using F8, when you know delRange is going to equal Nothing. You will notice that when you get to this line only one part will highlight yellow

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

As it is checking whether "Not delRange Is Nothing" ... in this case, since this statement is not true, it will skip the next part and continue to this line:

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

Since there is nothing to Paste, VBA gives you the error "PasteSpecial method of Range class failed". You need to rearrange your IF statement like this:

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

That way the "paste" statement is inside the "IF" statement and will not run unless delRange is not nothing.

 

[lrp20]

Hi svendiamond, apologies for the slow response. Thank you very much for taking the time to explain that to me, I've just rearranged the IF statement and it works! A huge thank you for helping me out of a hole!

 

[svendiamond]

Hi svendiamond, apologies for the slow response. Thank you very much for taking the time to explain that to me, I've just rearranged the IF statement and it works! A huge thank you for helping me out of a hole!

You're welcome. And thanks to StephenCrump for pointing out the error.