Unsuccessful at Cutting VALUES and pasting in Last Newly added Row of table

[sashol3]

I am uncertain at this point why I am successfully able to cut and paste but when I try to use paste special to paste values I am unable to to. Also I have to repeat this multiple times with multiple cells that are scattered in seperate ranges. I wish I could define each range with a variable. However, when I

Dim Calclogs_sheet As Worksheet
Dim Calclogs_table As ListObject
Dim Last_Row As Long

Set Calclogs_sheet = Sheets("Calclogs_sheet")
Set Calclogs_table = Sheets("Calclogs_sheet").ListObjects("Calclogs_table")
Set Calculator = Sheets("Calculator")
Last_Row = Calclogs_table.Range.Rows.Count
ticker = Calculator.Range("B1:C1")

'Successful but I only want value pasted'
With Calclogs_table
    Set new_row = .ListRows.Add.Range
    Calculator.Range("B1:C1").Cut Destination:=Calclogs_table.Range(Last_Row, "A").Offset(0, 4)
End With

'Unsuccessful attempt when pasting values'
With Calclogs_table
    Set new_row = .ListRows.Add.Range
    Set listCols = .ListColumns
    Calculator.Range("B1:C1").Cut
    Calclogs_table.Range(Last_Row, "A").Offset(0, 4).PasteSpecial Paste:=xlPasteValues
End With

'Also: How do i use a variable for a cell's range?- This does not work:'
ticker.Cut Destination:=Calclogs_table.Range(Last_Row, "A").Offset(0, 4)

 

[Alex Blakenburg]

Cut does not support a PasteSpecial operation.
(Just try doing it Excel)
So just use Copy, PasteSpecial then Calculator.Range("B1:C1").ClearContents

If you want to use your ticker variable you need.

Dim ticker As Range
Set ticker = Calculator.Range("B1:C1")

    ticker.Copy
    Calclogs_table.Range(Last_Row, "A").Offset(0, 4).PasteSpecial Paste:=xlPasteValues
    ticker.ClearContents

Although the code is adding a new row, it is pasting to the last row that has values on it in column A not to the new row, is that what you intended.

 

[sashol3]

Much appreciated. Its hard to believe I never was able to come across paste specials lack of support to the cut method.

No that is not what I intended. I wanted the new values on the newly added row. I suppose that is going to be my next challenge.

 

[sashol3]

Much appreciated. Its hard to believe I never was able to come across paste specials lack of support to the cut method.

No that is not what I intended. I wanted the new values on the newly added row. I suppose that is going to be my next challenge.

Also, is there a shorter way of defining data type AS range for a variable. It seems weird that I have to do the same thing for the 13 variables I want to make.

 

[Peter_SSs]

A few other points/suggestions.

• You have declared some, but not all, of your variables. I would recommend always declaring all variables and you can force yourself to do that with this setting in Tools - Options in the vba window.
  
  
  
  
  
  
• With a table, there is no need to add a new row at the bottom before entering data into it. Adding data to the row immediately below the table automatically creates a new row (or rows) for the added data.
  
  
• .Range(Last_Row, "A") is not valid syntax to refer to a cell. It needs to be .Range("A" & Last_Row) or .Cells(Last_Row, "A")
  
  
• Cut not only removes the value from the cut cells but also any formatting. So if there does happen to be any formatting in 'ticker' then you may want .Clear rather than .ClearContents if you want to achieve the same outcome as Cut.
  
  
• Since you are just wanting the values from ticker, another option is instead of 'pushing' the values with copy/paste special, you can 'pull' them instead as shown in the code below.

See if this achieves your desired outcome.

Sub Testing()
  Dim Calclogs_sheet As Worksheet, Calculator As Worksheet
  Dim Calclogs_table As ListObject
  Dim Last_Row As Long
  Dim ticker As Range
  
  Set Calclogs_sheet = Sheets("Calclogs_sheet")
  Set Calclogs_table = Sheets("Calclogs_sheet").ListObjects("Calclogs_table")
  Set Calculator = Sheets("Calculator")
  Last_Row = Calclogs_table.Range.Rows.Count
  Set ticker = Calculator.Range("B1:C1")
  
  Calclogs_table.Range.Cells(Last_Row + 1, 5).Resize(ticker.Rows.Count, ticker.Columns.Count).Value = ticker.Value
  ticker.Clear
End Sub

 

[sashol3]

A few other points/suggestions.

• You have declared some, but not all, of your variables. I would recommend always declaring all variables and you can force yourself to do that with this setting in Tools - Options in the vba window.
  
  View attachment 40714
  
  
• With a table, there is no need to add a new row at the bottom before entering data into it. Adding data to the row immediately below the table automatically creates a new row (or rows) for the added data.
  
  
• .Range(Last_Row, "A") is not valid syntax to refer to a cell. It needs to be .Range("A" & Last_Row) or .Cells(Last_Row, "A")
  
  
• Cut not only removes the value from the cut cells but also any formatting. So if there does happen to be any formatting in 'ticker' then you may want .Clear rather than .ClearContents if you want to achieve the same outcome as Cut.
  
  
• Since you are just wanting the values from ticker, another option is instead of 'pushing' the values with copy/paste special, you can 'pull' them instead as shown in the code below.

See if this achieves your desired outcome.

Sub Testing()
  Dim Calclogs_sheet As Worksheet, Calculator As Worksheet
  Dim Calclogs_table As ListObject
  Dim Last_Row As Long
  Dim ticker As Range
 
  Set Calclogs_sheet = Sheets("Calclogs_sheet")
  Set Calclogs_table = Sheets("Calclogs_sheet").ListObjects("Calclogs_table")
  Set Calculator = Sheets("Calculator")
  Last_Row = Calclogs_table.Range.Rows.Count
  Set ticker = Calculator.Range("B1:C1")
 
  Calclogs_table.Range.Cells(Last_Row + 1, 5).Resize(ticker.Rows.Count, ticker.Columns.Count).Value = ticker.Value
  ticker.Clear
End Sub

Ok. Also I am trying to sum a range with the following code

Set quantity = Caclulator.WorksheetFunction.Sum(Range("C6:C9"))

Still getting debug errors(error 91 object variable or with variable block not set ) for ticker and probably the ones thereafter as well). And also, i dont know if my quantity variable formula will work. I am trying to sum a range of cells, and I am not sure how to specify the worksheet of the cells.

Dim Calculator As Worksheet
Dim Calclogs_sheet As Worksheet
Dim Calclogs_table As ListObject
Dim Last_Row As Long
Dim ticker As Range
Dim shareprice As Range
Dim quantity As Range
Dim stopp As Range
Dim stoppershare As Range
Dim mintarget As Range
Dim minprofit As Range
Dim mainprofit As Range
Dim riskamount As Range

Set ticker = Calculator.Range("B1:C1")
Set shareprice = Calculator.Range("B11:C11")
Set quantity = WorksheetFunction.Sum(Range("C6:C9"))
Set stopp = Calculator.Range("B10:C10")
Set stoppershare = Calculator.Range("B3:C3")
Set mintarget = Calculator.Range("D24:F24")
Set minprofit = Calculator.Range("E10:h10")
Set maintarget = Calculator.Range("B11:C11")
Set mainprofit = Calculator.Range("E14:h14")
Set riskamount = Calculator.Range("E8:H8")

 

[Peter_SSs]

Still getting debug errors(error 91 object variable or with variable block not set ) for ticker

Set ticker = Calculator.Range("B1:C1")

That is because you have declared Calculator as Worksheet, but not set it to be any worksheet.

Set quantity = WorksheetFunction.Sum(Range("C6:C9"))

You have declared quantity as a range but you are trying to set it to a number (sum)

Set maintarget = Calculator.Range("B11:C11")

You have not declared maintarget as any particular type of variable.

 

[sashol3]

"That is because you have declared Calculator as Worksheet, but not set it to be any worksheet."
There is something I am obv not understanding, I apologize. I have 2 worksheets that i am working with within the work book. I have declared each of them as a worksheet. One is "Calculator" and the other is "Calclogs_sheet" I dont understand why i would want to have Calculator be set to be ANY worksheet.

 

[Peter_SSs]

I dont understand why i would want to have Calculator be set to be ANY worksheet.

You need to tell vba which worksheet the variable Calculator applies to, just like you did in your first code

You do not have a line like that in your last code