0

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
Jonas
  • 121,568
  • 97
  • 310
  • 388
Jeremy Adams
  • 3
  • 3
  • Your syntax is incorrect for the Sheet.PasteSpecial method, but it would probably be simpler to use the Range.PasteSpecial method. See VBA Help for the proper use of these methods. – Ron Rosenfeld Dec 05 '14 at 17:30

1 Answers1

1

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
chancea
  • 5,858
  • 3
  • 29
  • 39
  • 1
    Worked perfectly! Thank you very much for your help. I wish I could vote, but sadly I cannot. However, you're ability to understand my question and answer it so precisely is not only appreciated but is very impressive. – Jeremy Adams Dec 05 '14 at 18:15