1

I have written a code but it only pastes the formulas present I want to paste it as values and not as formulas.

Sub lastRow()
Dim wsS1 As Worksheet 'Sheet1
Dim wsS2 As Worksheet 'sheet2
Dim lastR As Long, lastC As Long


Set wsS1 = Sheets("Instru Input")
Set wsS2 = Sheets("Process1")

 With wsS1
 lastR = .Range("A" & .Rows.Count).End(xlUp).Row - 4
 End With

With wsS2
lastC = .Cells(3, Columns.Count).End(xlToLeft).Column
Range(.Cells(3, 1).Address, .Cells(3, lastC).Address).AutoFill 
Destination:=Range(.Cells(3, 1).Address, .Cells(lastR, lastC).Address)

End With
End Sub

I am new to VBA I don't know how to paste the data as values I am not sure what to add to this code that it will only paste as values.

Paramjit Singh Rana
  • 814
  • 8
  • 29
j.doe
  • 43
  • 9

2 Answers2

0

First things first - put a dot before every Range() and Cells(), thus you would refer to With wsS2 correctly. Otherwise it refers to the ActiveSheet or the Worksheet, where the code is - VBA - Loop Through Multiple Worksheets and Apply Filter

Concerning the question - a quick way to solve it to add the following line at the end:

.Range(.Cells(3, 1), .Cells(lastR, lastC)).Value2 =.Range(.Cells(3, 1), .Cells(lastR, lastC)).Value2

before the End With. It would turn the formulas to values quite quickly. Or use the option from the comment, adding `, Type:= xlFillValues after the destination:

enter image description here

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • didnt work , it is still inserting data as formulas and not values – j.doe Jan 14 '19 at 05:06
  • "Range(.Cells(3, 1).Address, .Cells(3, lastC).Address).AutoFill .Range(.Cells(3, 1).Address, .Cells(lastR, lastC).Address), xlFillValues " i entered like this – j.doe Jan 14 '19 at 05:08
0

I guess this will do the trick. it not the high-level code ... but i think it will serve the expected output you can try this- 

Sub lastRow()

Dim wsS1 As Worksheet 'Sheet1
Dim wsS2 As Worksheet 'sheet2
Dim lastR As Long, lastC As Long


Set wsS1 = Sheets("Instru Input")
Set wsS2 = Sheets("Process1")

With wsS1
lastR = .Range("A" & .Rows.Count).End(xlUp).Row - 4
End With

With wsS2
lastC = .Cells(3, Columns.Count).End(xlToLeft).Column
Range(.Cells(3, 1).Address, .Cells(3, lastC).Address).AutoFill 
.Range(.Cells(3,1).Address, .Cells(lastR, lastC).Address)


End With

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End Sub