krcranfill
New Member
- Joined
- Mar 14, 2022
- Messages
- 9
- Office Version
- 2013
- Platform
- Windows
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)
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.
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)
VBA Code:
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.