VBA - Cut and Paste from Select Case

[mcintoan]

Hi all

Trying to have Excel move a pieces of data from a sheet called import to another sheet called format, however the below code comes up with either
"PasteSpecial method of range class failed" or "Cut method of Range class failed" when it tries to copy inside the select case portion.

I've put the failing code in Red/Bold font

any ideas?

Cheers

Sub format()

Application.ScreenUpdating = False


Dim fmt As Worksheet, inp As Worksheet, v0 As Worksheet


Set fmt = Worksheets("Format")
Set inp = Worksheets("import")
Set v0 = Worksheets("Parameters")


inp.Activate


''-----------------------------------------
'Find and insert Deliver to address
Range("B1:B20").Find("Deliver To", LookIn:=xlValues, MatchCase:=False).Activate


ActiveCell.Offset(0, 1).Activate
    If IsEmpty(ActiveCell) = True Then
        ActiveCell.Offset(1, 0).Activate
    End If


Dim dt1, dt2, dt3 As Variant
dt1 = Application.VLookup(ActiveCell.Value, Worksheets("Parameters").Range("A:D"), 2, False)
dt2 = Application.VLookup(ActiveCell.Value, Worksheets("Parameters").Range("A:D"), 2 + 1, False)
dt3 = Application.VLookup(ActiveCell.Value, Worksheets("Parameters").Range("A:D"), 2 + 2, False)


'VLookup Error Handling
If IsError(dt1) Then
dt1 = ActiveCell.Value
dt2 = "(Address Not programmed)"
dt3 = ""
End If


With fmt
.Range("C11").Value = dt1
.Range("C12").Value = dt2
.Range("C13").Value = dt3
End With


''-----------------------------------
'Body Content Insertion
Dim rng As Range, rng2 As Range
Dim mr, r1 As Long, r2 As Long
Dim rstart, rend As Long


'sets maximum row scan


mr = v0.Range("K1").Value


For r1 = 1 To mr Step 1


'finds the beginning of each POs by looking for the *** START OF PURCHASE ORDER - *** part
    If InStr(1, LCase(Range("C" & r1)), "start of purchase order") > 0 Then
        rstart = r1
          
          'get the last row of that section
        For r2 = r1 To mr Step 1
            If InStr(1, LCase(Range("C" & r2)), "end of purchase order") > 0 Then
                rend = r2
                Exit For
            End If
        Next r2


    For rstart = rstart To rend Step 1
    
    Dim onumb, ostat, over, odate, ddate As String
    
    
        Select Case Range("B" & rstart).Value
    
[COLOR=#b22222]            Case "Order Number:"
[B]                Range("C" & rstart).Cut Destination:=(fmt.Range("F14"))[/B][/COLOR]

                
            Case "Order Status:"
                Range("C" & rstart).Cut Destination:=(fmt.Range("F15"))
                
            Case "Order Version:"
                Range("C" & rstart).Cut Destination:=(fmt.Range("F10"))
            
            Case "Order Date:"
                Range("C" & rstart).Cut Destination:=(fmt.Range("F11"))
                
            Case "Delivery Date:"
                Range("C" & rstart).Cut Destination:=(fmt.Range("F12"))
                
            Case "Item Number"
                rstart = rstart + 1
                itemmove (rstart)
            
       End Select
   Next rstart


        
End If


Next r1
Debug.Print onumb
Debug.Print ostat
Debug.Print over
Debug.Print odate
Debug.Print ddate


Application.ScreenUpdating = True
End Sub

 

[mcintoan]

Please Disregard.
This works:

Case "Order Number:"
fmt.Range("F14").Value = Range("C" & rstart)

cheers

 

[MARK858]

Just for info so would

            Case "Order Number:"
                Range("C" & rstart).Cut Destination:=fmt.Range("F14")

(just removed the 2 extra brackets you had around the destination range).

Btw, it looks like you are declaring your variables incorrectly. In VBA each variable needs to be individually defined i.e.

Dim onumb, ostat, over, odate, ddate As String

is currently saying

Dim onumb as Variant, ostat as Variant, over as Variant, odate as Variant,  ddate As String

which I doubt is what you want.

 

[mcintoan]

Just for info so would

            Case "Order Number:"
                Range("C" & rstart).Cut Destination:=fmt.Range("F14")

(just removed the 2 extra brackets you had around the destination range).

Btw, it looks like you are declaring your variables incorrectly. In VBA each variable needs to be individually defined i.e.

Dim onumb, ostat, over, odate, ddate As String

is currently saying

Dim onumb as Variant, ostat as Variant, over as Variant, odate as Variant,  ddate As String

which I doubt is what you want.

Yeah i've actually deleted that entire line as it was redundant (was only using it for the debug.print)

Cheers for the cut & paste info as well

 

[MARK858]

Yeah i've actually deleted that entire line as it was redundant (was only using it for the debug.print)

Yes, but you are also doing the same in the lines below.

Dim mr, r1 As Long, r2 As Long
Dim rstart, rend As Long

but then I suppose you could possibly want mr and rstart as Variants rather than Longs

 

[mcintoan]

woops i missed that
thanks

Yes, but you are also doing the same in the lines below.

Dim mr, r1 As Long, r2 As Long
Dim rstart, rend As Long

but then I suppose you could possibly want mr and rstart as Variants rather than Longs