Paste Values

[marav08]

Hi
Quick question
I'm trying to copy a data from one worksheet to another.
I can now copy the data the only problem is it has a formula.
What im trying to paste is the values only and no longer will copy the formula.

Here is my code

Sub Button1_Click()

    Dim source As Worksheet
    Dim destination As Worksheet
    Dim emptyColumn As Long
    Set source = Sheets("AHT Per Skill Daily")
    Set destination = Sheets("AHT Summary")
    'find empty Column (actually cell in Row 1)'
    emptyColumn = destination.Cells(3, 1).End(xlToRight).Column
    
    If emptyColumn > 1 Then
        emptyColumn = emptyColumn + 1
    End If
    source.Range("AH20:AH36").Copy destination.Cells(3, emptyColumn)
    
 
    
 End Sub

 

[Logit]

https://www.wallstreetmojo.com/vba-paste-special/

 

[marav08]

Hi,

Tried to code the following.

Sub Button1_Click()


    Dim source As Worksheet
    Dim destination As Worksheet
    Dim emptyColumn As Long
    Set source = Sheets("AHT Per Skill Daily")
    Set destination = Sheets("AHT Summary")
   ' find empty Column (actually cell in Row 1)'
    emptyColumn = destination.Cells(3, 1).End(xlToRight).Column
    
    If emptyColumn > 1 Then
        emptyColumn = emptyColumn + 1
    End If
    source.Range("AH20:AH36").Copy destination.Cells(3, emptyColumn)
    source.Range("C3").PasteSpecial xlPasteValues
    
   
   
 End Sub

Pastespecial method of range class failed is the error

 

[Akuini]

Try replacing this:

    source.Range("AH20:AH36").Copy destination.Cells(3, emptyColumn)
    source.Range("C3").PasteSpecial xlPasteValues

with:

    source.Range("AH20:AH36").Copy 
    destination.Cells(3, emptyColumn).PasteSpecial xlPasteValues