Paste Row Values not Formulas

Question

Hi I've been searching around google for a while and not able to find the solution to my specific issue at the moment. So the problem is simple I want to select and copy a and paste only the values not the formulas generating the values.

I've checked : Copy Entire Row (Values not formulas) VBA

https://msdn.microsoft.com/en-us/library/aa195818(v=office.11).aspx

the current code I'm using is below I'm currently just using "OutputWorksheet.Paste" which is pasting the row but it also carries the formulas and tried "OutputWorksheet.PasteSpecial.xlPasteValues" but it keeps throwing "Compile Error: Expected Function or Variable"

Any help would be greatly appreciated or a better way of finding a Row based off the cboMADropDown value in one sheet copying the row and pasting to another sheet with just values no formulas.

With InputWorksheet                                                     ' Set sheet we want to search
Set FindRow = .Range("O:O").Find(What:=cboMADropDown, LookIn:=xlValues) ' Set FindRow container by searching Column F with cboMADropDown Value and look for the cell value to compare
End With                                                                ' End the With Statement
FindRowNumber = FindRow.Row                                             ' Set the FindRow row value to a number


InputWorksheet.Rows(FindRowNumber).EntireRow.Copy                       'Grab contents of entire row which is going to be updated
LastRow = OutputWorksheet.Range("A" & Rows.Count).End(xlUp).Row         'Finds the last blank Row on the MA tracking history sheet

OutputWorksheet.Activate                                                'Activate the worksheet so the .select command works with no errors
OutputWorksheet.Rows(LastRow).Select                                    'Select last blank row from MA tracking history
OutputWorksheet.Paste                        'Paste contents of the clipboard to backup
OutputWorksheet.Range("O" & LastRow).Value = TimeStamp                  'Add time stamp for the update

Did you try OutputWorksheet.PasteSpecial xlPasteValues without . between PasteSpecial and xlPasteValues? — Vincent G
I've tried it with OutputWorksheet.PasteSpecial xlPasteValues and I get Run-time error '1004': Method 'PasteSpecial' of Object'_Worksheet' failed — Kaneki Byte
Re read the MSDN page you've linked. You are using the Worksheet PasteSpecial, while you should probably use the Range one: OutputWorksheet.Rows(LastRow).PasteSpecial xlPasteValues — Vincent G
Thanks Vincent I think this is going to be a good learning experience as the copy and paste I'm doing seems a little clunky. The rationale for not using .Range is that I have InputWorksheet.Rows(FindRowNumber).EntireRow.Copy on the clipboard not stored in a Array or Variable so it should be just paste but I'm trying to look for an operator which says just paste Text only rather then paste all the source formatting and formulas — Kaneki Byte

FAB FAB · Accepted Answer · 2018-05-24T16:00:25

Just my 2 cents here... your lastrow gives you the last occupied row, not the first row available... if I'm not wrong? so you probably need something like this to actually get the first blank row:

LastRow = OutputWorksheet.Range("A" & Rows.Count).End(xlUp).Row + 1

Then, you don't need to copy/paste the values, you can simply do rangeB.Value = rangeA.Value - and this will only copy the values, not the formulaes. In your case, that would look like this:

With InputWorksheet                                                     ' Set sheet we want to search
    Set FindRow = .Range("O:O").Find(What:=cboMADropDown, LookIn:=xlValues) ' Set FindRow container by searching Column F with cboMADropDown Value and look for the cell value to compare
End With                                                                ' End the With Statement
FindRowNumber = FindRow.Row                                             ' Set the FindRow row value to a number

With OutputWorksheet
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row                 'Finds the last blank Row on the MA tracking history sheet
    .Rows(LastRow).Value = InputWorksheet.Rows(FindRowNumber).Value
    .Range("O" & LastRow).Value = TimeStamp                  'Add time stamp for the update
End With

Is worth mentioning that given you don't really need the full row (i might be wrong here), you could, and better specify the range more clearly (unless you have more values beyond column "O":

FindRowNumber = InputWorksheet.Range("O:O").Find(What:=cboMADropDown, LookIn:=xlValues).Row 'assign the row number directly to the variable (as with the LastRow, unless you need differently for other reasons)
With OutputWorksheet
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LastRow & ":N" & LastRow).Value = InputWorksheet.Range("A" & FindRowNumber & ":N" & FindRowNumber).Value
    .Range("O" & LastRow).Value = TimeStamp
End With

Paste Row Values not Formulas

1 Answers