excel cannot complete this task when paste values from temp sheet

usually you will be better off by equating the values of a particular range. You Are selecting 9 columns, but i would imagine you don't need all of it.

Try this:

Option Explicit

Sub exampleCopier()


'make a couple of range variables'
Dim targetR As Range, sourceR As Range

'instead of copy/paste just set the range'
    Set sourceR = ThisWorkbook.Worksheets("temp_for_calcs").UsedRange

'make them the same size'
    Set targetR = tables_sheet.Cells(1, 1).Resize(sourceR.Rows.Count, sourceR.Columns.Count)

'equate the values, replaces copy/pasting'
    targetR.Value = sourceR.Value
End Sub

-----------
Using Select/Copy/PasteSpecial is not impossible, and I realize I didn't really answer your exact question. Just usually you never have to do that - making use of range variables is sooooooooo much simpler once you are introduced to it.

Also, using sheetName.usedRange is extremely handy - but only if your worksheet in this case contains only the items you want (which, would be a good principle to design things around). That being said, if you have more stuff on the worksheet outside of the range you want to copy over, we can alter that statement to only capture the parts you need to copy.

Good to hear, please mark this question as "Solved"


To mark your thread solved do the following:

-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved