Faster copy paste function between sheets

[krcranfill]

Hello Everyone,

I am working on gathering some data on jobs that require very little assembly time and I am writing some script to automate that. In an effort to speed things up I am trying to resize the destination table to match the size of the data prior to copying. However, something is wrong with my code for resizing the sheet. This is my first time to try resizing a table with VBA and I'm feeling a bit lost by the information I have found online (specifically the resizing tables section of this: The VBA Guide To ListObject Excel Tables — TheSpreadsheetGuru)

Sub Zero_time_ops()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    Worksheets("0 Time Operations").Range("A2:B10000").ClearContents
    Worksheets("0 Time Operations").Range("C3:H10000").ClearContents
    Worksheets("0 Time Operations").ListObjects("Zero_Time_Ops").Resize Range("$A$1:$G$2")
    ' Clear Previous data and resize table
    
    Sheets("SA").Range("SA_Overview").AutoFilter Field:=3, Criteria1:="<" & Worksheets("Current Month Prod. Capacity").Range("zero_time_std").Value2, Operator:=xlFilterValues
    'filter to show only low and zero time values
    
    Set copysrc = Worksheets("SA").ListObjects("SA_Overview").ListColumns(1).DataBodyRange
    'set source from which to copy
    
    copy_src_len = copysrc.Rows.Count
     Set copy_dest_rng = Worksheets("0 Time Operations").ListObjects("Zero_Time_Ops[#all]").Resize(copy_src_len, 7)
       Worksheets("0 Time Operations").ListObjects("Zero_Time_Ops").Resize copy_dest_rng
    'resize destination table to match data size (to speed up copy operation)
    
    Worksheets("0 Time Operations").ListObjects("Zero_Time_Ops").ListColumns(1).DataBodyRange
    'set destination range for copy
    
    copy_dest_rng.Value2 = copysrc.Value2
    'Copy Data
End Sub

The problem is that on the line:
Set copy_dest_rng = Worksheets("0 Time Operations").ListObjects("Zero_Time_Ops[#all]").Resize(copy_src_len, 7)
I get a "Runtime error '9' Subscript out of range" error if I include the "[#all]" part and if I remove it and only have it as:
Set copy_dest_rng = Worksheets("0 Time Operations").ListObjects("Zero_Time_Ops").Resize(copy_src_len, 7)
then I get a "Run-time Error '450', Wrong number of arguments or invalid property assignment" error.

(All variables used in this portion of my code are defined publicly)
Any advice would be appreciated.

 

[krcranfill]

I realize on the code I copied in I forgot to fix the destination target range in the second to last line of code , but that's easy enough (i think) to fix and is not really the problem I'm facing.

 

[krcranfill]

So I have managed to get the resizing to work by doing it like this:

copy_src_len = copysrc.Rows.Count
Worksheets("0 Time Operations").ListObjects("Zero_Time_Ops").Resize Range("A1:G" & copy_src_len + 1)

But its grabbing all the cells when it copies instead of only the filtered results. I tried adding a ".SpecialCells(xlCellTypeVisible)" to the end of the line where I set the copy range, but for some reason this makes it think that the range only has 1 row (should be >50)