Trouble pasting only values in VBA

0
votes

I'm trying to paste only the values to the target cell instead of the formulas and haven't had any success after much googling. This is my first real venture into VBA so I'm unsure of where to go next. My current code is:

Function freeCell(r As Range) As Range
' returns first free cell below r
  Dim lc As Range, Tracking As Worksheet     ' last used cell on sheet
  Set lc = r.Offset(40, 6).End(xlUp).Offset(1, 0)
  Set freeCell = lc
End Function


Sub doIt()
  Dim tCell As Range, sh As Worksheet
  Sheets("Bundler").Range("G20").Copy
  Set sh = Sheets(Range("G73").Value)
  Set tCell = freeCell(sh.Range("A3"))
  If tCell.Row < 19 Then Set tCell = tCell.Offset(19 - tCell.Row)
  sh.Paste tCell
End Sub

sh.Paste tCell pastes into the correct cell, but how can I convert that to only values? I'm aware .PasteSpecial xlPasteValues needs to be used but I'm not sure how to format it.

2
vbaexcel

2 Answers

2
votes
Sub doIt()
  Dim tCell As Range, sh As Worksheet
  Sheets("Bundler").Range("G20").Copy
  Set sh = Sheets(Range("G73").Value)
  Set tCell = freeCell(sh.Range("A3"))
  If tCell.Row < 19 Then Set tCell = tCell.Offset(19 - tCell.Row)
  tCell.PasteSpecial Paste:=xlPasteValues
End Sub
0
votes

You can do this without a copy and paste:

Sub doIt()
  Dim tCell As Range, sh As Worksheet
  Set sh = Sheets(Range("G73").Value)
  Set tCell = freeCell(sh.Range("A3"))
  If tCell.Row < 19 Then Set tCell = tCell.Offset(19 - tCell.Row)
  tCell.Formula = Sheets("Bundler").Range("G20").Text
End Sub

Change .text to .value if you want it as a value