Pasting Values in a table

[davidepstein22]

Hello,

I have an Excel table that I update with new information each month. When new data is added (new rows), using VBA I enter a vlookup into the new rows and the correct data is retrieved. However, when I select the new rows and try to overwrite the vlookup formulas with values (Paste Special Values), the amounts all change to zero (0). Interestingly, if I walk-through the code in debug it works correctly. I tried adding doevents, but that is not working. I suspect it has to do with the fact that I working within a table, but I don't know why this is an issue. Any help would be greatly appreciated. Thank you!

This code to add the vlookup works correctly.
ActiveSheet.Cells(tblSalesLastRow + 1, tblSalesUnitsSoldcolumn).Select
Selection.Formula = "=IFNA(VLOOKUP(Sales[@[Model '#]],'Last Months Sales'!$h$2:$i$1001,2,FALSE),0)"
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

This code does NOT work and I have tried many variations.
ActiveSheet.Cells(tblSalesLastRow + 1, tblSalesUnitsSoldcolumn).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Thanks,
Dave

 

[Jeffrey Mahoney]

Do you have code above that is turning automatic calculation off?
application.Calculation = xlCalculationManual
If so, you'll need to calculate the range after copying the formula down and before pasting as values

 

[davidepstein22]

Do you have code above that is turning automatic calculation off?
application.Calculation = xlCalculationManual
If so, you'll need to calculate the range after copying the formula down and before pasting as values

Hi Jeffrey - I do not have any code that turns calculation to manual - it is always automatic. I continued to work on the code and realized that when I simply paste-values after selecting the top-most row it worked correctly. Perhaps I am missing something basic, but I select all of the rows with the formula to be "careful", which was apparently the problem. Any thoughts.

Thanks,
Dave

 

[Jeffrey Mahoney]

I'm grasping at straws 'cause everything looks fine. Try this though. Shorter code

Sub CopyAndPAste()
  Dim Rng As Range
  Dim Cel As Range
  Dim Sht As Worksheet
  
  Set Sht = ActiveSheet
  Set Cel = Sht.Cells(tblSalesLastRow + 1, tblSalesUnitsSoldcolumn)
  Set Rng = Sht.Range(Cel, Cel.End(xlDown))
  Rng.Formula = "=IFNA(VLOOKUP(Sales[@[Model '#]],'Last Months Sales'!$h$2:$i$1001,2,FALSE),0)"
  Rng.Value = Rng.Value2
  
  
End Sub

 

[davidepstein22]

I'm grasping at straws 'cause everything looks fine. Try this though. Shorter code

Sub CopyAndPAste()
  Dim Rng As Range
  Dim Cel As Range
  Dim Sht As Worksheet
 
  Set Sht = ActiveSheet
  Set Cel = Sht.Cells(tblSalesLastRow + 1, tblSalesUnitsSoldcolumn)
  Set Rng = Sht.Range(Cel, Cel.End(xlDown))
  Rng.Formula = "=IFNA(VLOOKUP(Sales[@[Model '#]],'Last Months Sales'!$h$2:$i$1001,2,FALSE),0)"
  Rng.Value = Rng.Value2
 
 
End Sub

 

[davidepstein22]

I appreciate the "clean" version of the code you supplied, but unfortunately it returned a zero (0) for all of the vlookups, which is incorrect. I believe this has something to do with the fact the data resides in an Excel table. I will play with your code over the next day or two and let you know if I can find out why it is not working. Thank you.