Search code examples
excelvbacellcopy-paste

Excel: how to paste "values" to first blank cell?


Considering that I have already read through another thread explaining "how to paste to first blank cell"... here I need to know how to paste "value". Example:

 Dim sh As Worksheet, tCell As Range
 Sheets("Lunch Extend").Range("B4:F31").Copy
 Set sh = Sheets("Backup")
 Set tCell = FreeCell(sh.Range("B3"))
 sh.Paste PasteSpecial xlValues tCell

However, the last line does not read correctly. Any help?

sh.Paste tCell

...works, but I need values. Thanks.

Adding FreeCell function here...

Function FreeCell(r As Range) As Range
' returns first free cell below r
  Dim lc As Range     ' last used cell on sheet
  Set lc = r.Offset(1000, 0).End(xlUp).Offset(1, 0)
  Set FreeCell = lc
End Function

Solution

  • Assuming that FreeCell is a function you wrote and that it works. Try this:

    Dim sh As Worksheet, tCell As Range
      Sheets("Lunch Extend").Range("B4:F31").Copy
      Set sh = Sheets("Backup")
      Set tCell = FreeCell(sh.Range("B3"))
    
      tCell.PasteSpecial Paste:=xlPasteValues