VBA: Using PasteSpecial with .Cut?

[Aus]

Hi Everyone,

Beginner with VBA here. I'm having trouble with a simple macro I'm writing. Everything works as expected except I get errors whether using PasteSpecial(xlPasteValues). I've tried worksheets.range and worksheets.cells. Is it because I'm using Cut rather than Copy?
Thanks.

Dim lRow As Long, lCol As Long, lMax As Long
    'Add new row to Ledger
   
    Worksheets("Ledger").ListObjects("tbl_ledger").ListRows.Add
    'Find first empty row in Ledger
 
    For lCol = 1 To 3
   
        lRow = Worksheets("Ledger").Cells(Rows.Count, lCol).End(xlUp).Row
       
        If lRow > lMax Then lMax = (lRow - 1)
       
    Next lCol

    'Copy input from Dashboard Input Fields to next empty row in Ledger
   
    Worksheets("Dashboard").Range("B2:D2").Cut Worksheets("Ledger").Cells((lMax + 1), 1)
   
    'Convert value to negative, if not already negative
   
    If Worksheets("Ledger").Cells((lMax + 1), 3).Value > 0 Then Worksheets("Ledger").Cells((lMax + 1), 3).Value = Worksheets("Ledger").Cells((lMax + 1), 3).Value * -1

 

[NARAYANK991]

Hi ,

Is it possible for you to upload your workbook ?

Narayan

 

[Aus]

Hi Narayan,

I've uploaded the file as requested. Thanks.

 

[NARAYANK991]

Hi ,

What is the error you get ? I don't get any errors at my end ; I have tried both Credit and Debit.

Narayan

 

[Aus]

Thanks for your help so far. Sorry for the confusion.

The two working macros in that worksheet are using the default paste operation. I'm trying to change that to pastevalues but after attempting a few different methods I've picked up elsewhere I can't get anything to work. The last unsuccessful attempt is saved as Test_PstSpd in the Workbook. Code below.

Depending on what I try my error messages are:

"Object doesn't support this property" - when I try sheet.range.cells.pastespecial

"Unable to get the PasteSpecial property of the Range class" - when I try sheet.cells.pastespecial

I've tried so many variations that the test code has become a bit jumbled.

Sub BtnAddDebit()

    'Add new row to Ledger
    Worksheets("Ledger").ListObjects("tbl_ledger").ListRows.Add
    'Find first empty row in Ledger
    Dim lRow As Long, lCol As Long, lMax As Long
    For lCol = 1 To 3
 
        lRow = Worksheets("Ledger").Cells(Rows.Count, lCol).End(xlUp).Row
     
        If lRow > lMax Then lMax = (lRow - 1)
     
    Next lCol

    'Copy input from Dashboard Input Fields to next empty row in Ledger

        With Worksheets("Dashboard")
        .Range("B2:D2").Cut
        End With
        With Worksheets("Ledger")
        .Range("tbl_ledger").End (xlDown)
        .PasteSpecial Paste:=xlPasteValues
        End With

  'Convert value to negative
    Worksheets("Ledger").Cells((lMax + 1), 3).Value = Worksheets("Ledger").Cells((lMax + 1), 3).Value * -1
 
 
End Sub

 

[NARAYANK991]

Hi ,

If you are posting regarding the macro named Test_PstSpcl.BtnAddDebit , you are right , it is because of the usage of Cut rather than Copy.

Forget VBA , just use the Excel menu options within the worksheet itself ; when you do CTRL C for Copy , all of the PasteSpecial features / options are available.

However , when you do CTRL X for Cut , only Paste is available ; PasteSpecial itself is not available , leave alone its different options.

Thus , if you are using VBA and you use Cut , you can only Paste , not PasteSpecial.

Narayan

 

[Aus]

That's right. Just trying to work out how to cut/paste special. So the only alternative would be to copy contents, paste special to new range, clear contents of the original range, is that right?

 

[NARAYANK991]

Hi ,

I think so. Otherwise , use the Paste command instead of PasteSpecial ; after all , PasteSpecial is only relevant if you do not wish formulae or formats to be copied.

In this case , because you are performing the operation on a table , the table style will be retained even if you do a Paste , provided the range B2:D2 in the Dashboard tab does not have any format ; however , because the background has been formatted to black , the first time the table cells will not be formatted , but thereafter they will be.

I think doing a Copy and then clearing the range B2:D2 is a better option.

Narayan

 

[Aus]

Exactly the problem that caused me to start looking at PasteSpecial. I'll move on with Copy/Paste/Clear.

Thanks for your help, Narayan.