Excel VBA - Copy Paste As Values

[luksim]

Hello,

I am trying to make a VBA code to Copy and Paste particular worksheet range AS VALUES. Does anyone have that kind of code stage/action created?

Appreciate!

e.g as Copy Paste with All Formats code:
Dim wb, ws As Object
Dim excel, sheet, range,PasteRange As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(CopyCellReference)
range.Select()
range.Copy

PasteRange=sheet.Range(PasteCellReference)
PasteRange.Select()
sheet.Paste

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
End Try

 

[jrprajapati]

What do you mean by you want to paste as VALUE?

 

[luksim]

@jrprajapati
I mean that I don't want to copy paste any formulas from one cell to another. There's option when you right click to paste "As Values", which means it only takes text or numbers from wanted cell.

 

[luksim]

@jrprajapati , maybe you could help me with this one?

This is what I've tried now, but the error pops out: ERROR: Exception : Failed to copy worksheet: PasteSpecial method of Worksheet class failed

CODE:
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, source, destination, cells, cell As Object
Dim xlPasteValues as Integer = -4163

Try

sw = GetWorkbook(Handle, Source_Workbook)
dw = GetWorkbook(Handle, Source_Workbook)

ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
ds = GetWorksheet(Handle, Source_Workbook, Destination_Worksheet)

sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
cell = excel.ActiveCell

If Source_Range="" Then
cells = sheet.Cells
Destination_Range = "A1"
Else
cells = sheet.Range(Source_Range)
End If

cells.Select()
source = excel.Selection
source.Copy()
cell.Select()

dw.Activate()
ds.Activate()
sheet = excel.ActiveSheet
cell = excel.ActiveCell
destination = sheet.Range(Destination_Range)
destination.Select()
'Paste data as values
sheet.PasteSpecial(xlPasteValues)
cell.Select()

My.Computer.Clipboard.Clear()

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
source = Nothing
destination = Nothing
cells = Nothing
cell = Nothing
End Try

 

[gil.silva]

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, source, destination, cells, cell As Object

Try

sw = GetWorkbook(Handle, Source_Workbook)
dw = GetWorkbook(Handle, Destination_Workbook)

ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
ds = GetWorksheet(Handle, Destination_Workbook, Destination_Worksheet)

sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
cell = excel.ActiveCell

If Source_Range="" Then
    cells = sheet.Cells
    Destination_Range = "A1"
Else
    cells = sheet.Range(Source_Range)
End If

dw.Activate()
ds.Activate()

[B]ds.Range(Destination_Range).Value = ss.Range(Source_Range).Value[/B]

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    sw = Nothing
    ss = Nothing
    dw = Nothing
    ds = Nothing
    excel = Nothing
    sheet = Nothing
    source = Nothing
    destination = Nothing
    cells = Nothing
    cell = Nothing
End Try

Try the above code, it has been working for me.
The main difference is the line: ds.Range(Destination_Range).Value = ss.Range(Source_Range).Value