How do you copy a formula result as a value instead of the formula

[NPigna]

Hi, I have never done coding. Someone told me how to write this. After finishing it I found I need something to copy value and not the formula. I found a DIM(the last one in the code) that might work but don't know how to finish it to read everything. Can someone tell me what exactly I need to write? Sorry its messy I have been adding notes...
Sub FillProposal()

'Define variables
Dim pricingSheet As Workbook
Dim macroWorkbook As Workbook
Dim wdapp As Word.Application
Dim proposalTemplate As Word.Document
Dim listLength As Integer
Dim strText As String
strText = Range("A1").Text
Dim dblNum As Double
dblNum = Range("A1").Value2

'Identify workbooks and open templates
Set pricingSheet = ActiveWorkbook
Windows("macro.xlsm").Activate
Set macroWorkbook = ActiveWorkbook
Set wdapp = New Word.Application
'Change file path when template is moved or renamed
Set proposalTemplate = wdapp.Documents.Open("C:\Users\natpi\Documents\CODED PROPOSAL TEMPLATE1.docx")
'C:\Users\Fine Arts\Documents\PRECISION SAFE SIDEWALKS\PSS TEMPLATES\PROPOSAL TEMPLATE REV 4-9-19 with excel pts (1).docx")
wdapp.Visible = True


'Find and replace Summary table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(1))
For i = 3 To listLength
wdapp.Activate
With wdapp.Selection.Find
.Text = macroWorkbook.Sheets(1).Cells(i, 1)
.Replacement.Text = pricingSheet.Sheets(1).Cells(macroWorkbook.Sheets(1).Cells(i, 2), macroWorkbook.Sheets(1).Cells(i, 3))
.Execute Replace:=wdReplaceAll
End With
Next
'to copy a formula result as a value instead of the formula, instead of '.Paste', use '.PasteSpecial'
'ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")

'with this line:
'Worksheets("Positions").Range("W2").PasteSpecial xlPasteValues

'Find and replace Green table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(4))
For i = 3 To listLength
wdapp.Activate
With wdapp.Selection.Find
.Text = macroWorkbook.Sheets(1).Cells(i, 4)
.Replacement.Text = pricingSheet.Sheets(2).Cells(macroWorkbook.Sheets(1).Cells(i, 5), macroWorkbook.Sheets(1).Cells(i, 6))
.Execute Replace:=wdReplaceAll
End With
Next



End Sub

 

[rondeondo]

can you highlight the part of the code you're asking for help with? I see a pastespecial line that does paste values only. Is this not working?
if you're doing cell by cell tests you can use cell.formula = cell.value to set the formula to just be the value.

 

[Beyond_avarice]

The best thing to do is not copy and paste, but rather assign values from one source to a new location.
eg

Sheets("Sheet1").Range("A1").Value = Sheets("Sheet2").Range("A1").Value

 

[NPigna]

can you highlight the part of the code you're asking for help with? I see a pastespecial line that does paste values only. Is this not working?
if you're doing cell by cell tests you can use cell.formula = cell.value to set the formula to just be the value.

Thank you so much for replying! The stuff in green are notes I added and codes I found on line that might help with what I want. The last DIM I added and it for that also but didnt finish but it didnt stop the code from working. The person who helped me wrote the code and later realized he could use dfferent language for columns so we had to use numbers instead of letters. I have another spreadsheet that I pick the columns i need ed data from. Here is a pix of part of it.

So to be honest I am not sure where the code needs to go my code doesnt use the word paste

Sub FillProposal()

'Define variables
Dim pricingSheet As Workbook
Dim macroWorkbook As Workbook
Dim wdapp As Word.Application
Dim proposalTemplate As Word.Document
Dim listLength As Integer
Dim strText As String
strText = Range("A1").Text
Dim dblNum As Double
  dblNum = Range("A1").Value2

'Identify workbooks and open templates
Set pricingSheet = ActiveWorkbook
Windows("macro.xlsm").Activate
Set macroWorkbook = ActiveWorkbook
Set wdapp = New Word.Application
'Change file path when template is moved or renamed
Set proposalTemplate = wdapp.Documents.Open("C:\Users\natpi\Documents\CODED PROPOSAL TEMPLATE1.docx")
wdapp.Visible = True

'Find and replace Summary table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(1))
For i = 3 To listLength
    wdapp.Activate
    With wdapp.Selection.Find
        .Text = macroWorkbook.Sheets(1).Cells(i, 1)
        .Replacement.Text = pricingSheet.Sheets(1).Cells(macroWorkbook.Sheets(1).Cells(i, 2), macroWorkbook.Sheets(1).Cells(i, 3))
        .Execute Replace:=wdReplaceAll                              '(i think this is where the code needs to go)
    End With
Next
'to copy a formula result as a value instead of the formula, instead of '.Paste', use '.PasteSpecial'
'ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")

'with this line:
'Worksheets("Positions").Range("W2").PasteSpecial xlPasteValues

'Find and replace Green table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(4))
For i = 3 To listLength
    wdapp.Activate
    With wdapp.Selection.Find
        .Text = macroWorkbook.Sheets(1).Cells(i, 4)
        .Replacement.Text = pricingSheet.Sheets(2).Cells(macroWorkbook.Sheets(1).Cells(i, 5), macroWorkbook.Sheets(1).Cells(i, 6))
        .Execute Replace:=wdReplaceAll        '(i think this is where the code needs to go)
    End With
Next



End Sub

 

[NPigna]

Thank you so much for replying! The stuff in green are notes I added and codes I found on line that might help with what I want. The last DIM I added and it for that also but didnt finish but it didnt stop the code from working. The person who helped me wrote the code and later realized he could use dfferent language for columns so we had to use numbers instead of letters. I have another spreadsheet that I pick the columns i need ed data from. Here is a pix of part of it.

So to be honest I am not sure where the code needs to go my code doesnt use the word paste

View attachment 8471

Sub FillProposal()

'Define variables
Dim pricingSheet As Workbook
Dim macroWorkbook As Workbook
Dim wdapp As Word.Application
Dim proposalTemplate As Word.Document
Dim listLength As Integer
Dim strText As String
strText = Range("A1").Text
Dim dblNum As Double
  dblNum = Range("A1").Value2

'Identify workbooks and open templates
Set pricingSheet = ActiveWorkbook
Windows("macro.xlsm").Activate
Set macroWorkbook = ActiveWorkbook
Set wdapp = New Word.Application
'Change file path when template is moved or renamed
Set proposalTemplate = wdapp.Documents.Open("C:\Users\natpi\Documents\CODED PROPOSAL TEMPLATE1.docx")
wdapp.Visible = True

'Find and replace Summary table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(1))
For i = 3 To listLength
    wdapp.Activate
    With wdapp.Selection.Find
        .Text = macroWorkbook.Sheets(1).Cells(i, 1)
        .Replacement.Text = pricingSheet.Sheets(1).Cells(macroWorkbook.Sheets(1).Cells(i, 2), macroWorkbook.Sheets(1).Cells(i, 3))
        .Execute Replace:=wdReplaceAll                              '(i think this is where the code needs to go)
    End With
Next
'to copy a formula result as a value instead of the formula, instead of '.Paste', use '.PasteSpecial'
'ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")

'with this line:
'Worksheets("Positions").Range("W2").PasteSpecial xlPasteValues

'Find and replace Green table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(4))
For i = 3 To listLength
    wdapp.Activate
    With wdapp.Selection.Find
        .Text = macroWorkbook.Sheets(1).Cells(i, 4)
        .Replacement.Text = pricingSheet.Sheets(2).Cells(macroWorkbook.Sheets(1).Cells(i, 5), macroWorkbook.Sheets(1).Cells(i, 6))
        .Execute Replace:=wdReplaceAll        '(i think this is where the code needs to go)
    End With
Next



End Sub

Thank you so much for replying! The stuff in green are notes I added and codes I found on line that might help with what I want. The last DIM I added and it for that also but didnt finish but it didnt stop the code from working. The person who helped me wrote the code and later realized he could use dfferent language for columns so we had to use numbers instead of letters. I have another spreadsheet that I pick the columns i need ed data from. Here is a pix of part of it.

So to be honest I am not sure where the code needs to go my code doesnt use the word paste

View attachment 8471

Sub FillProposal()

'Define variables
Dim pricingSheet As Workbook
Dim macroWorkbook As Workbook
Dim wdapp As Word.Application
Dim proposalTemplate As Word.Document
Dim listLength As Integer
Dim strText As String
strText = Range("A1").Text
Dim dblNum As Double
  dblNum = Range("A1").Value2

'Identify workbooks and open templates
Set pricingSheet = ActiveWorkbook
Windows("macro.xlsm").Activate
Set macroWorkbook = ActiveWorkbook
Set wdapp = New Word.Application
'Change file path when template is moved or renamed
Set proposalTemplate = wdapp.Documents.Open("C:\Users\natpi\Documents\CODED PROPOSAL TEMPLATE1.docx")
wdapp.Visible = True

'Find and replace Summary table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(1))
For i = 3 To listLength
    wdapp.Activate
    With wdapp.Selection.Find
        .Text = macroWorkbook.Sheets(1).Cells(i, 1)
        .Replacement.Text = pricingSheet.Sheets(1).Cells(macroWorkbook.Sheets(1).Cells(i, 2), macroWorkbook.Sheets(1).Cells(i, 3))
        .Execute Replace:=wdReplaceAll                              '(i think this is where the code needs to go)
    End With
Next
'to copy a formula result as a value instead of the formula, instead of '.Paste', use '.PasteSpecial'
'ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")

'with this line:
'Worksheets("Positions").Range("W2").PasteSpecial xlPasteValues

'Find and replace Green table
listLength = Application.WorksheetFunction.CountA(macroWorkbook.Sheets(1).Columns(4))
For i = 3 To listLength
    wdapp.Activate
    With wdapp.Selection.Find
        .Text = macroWorkbook.Sheets(1).Cells(i, 4)
        .Replacement.Text = pricingSheet.Sheets(2).Cells(macroWorkbook.Sheets(1).Cells(i, 5), macroWorkbook.Sheets(1).Cells(i, 6))
        .Execute Replace:=wdReplaceAll        '(i think this is where the code needs to go)
    End With
Next



End Sub

I'm sorry. I am totally out of my depth. I do not even know where to put the code tags that were requested. If I could use this formula.
CODE-cell.formula = cell.value to set the formula to just be the value
Where would I put it and how would I attach it to a certain cell? and can I use it for the whole spreadsheet? If you think this is too difficult to explain to someone who doesn't know coding Its fine. I can try to find someone in person that can help me.

 

[rondeondo]

Hi N
can you outline what it is you're trying to achieve at a higher level? there may be a better way to do it. Fault finding code is OK once you've established what you're trying to do needs the code.

Ron

 

[NPigna]

Hi N
can you outline what it is you're trying to achieve at a higher level? there may be a better way to do it. Fault finding code is OK once you've established what you're trying to do needs the code.

Ron

I have a word document that has blanks I need to fill in with data from an excel spreadsheet. I put the sheet, row, and column in each blank. Then we made a code that imports the data from the spreadsheet to the word document. It works but some of the data imported becomes a number with decimals like 56.33345 rather than what is in the cell like just the 56. I figured there must be a code to right for that. Thanks for trying to help me.