Faster copy paste function between sheets

krcranfill

New Member
Joined
Mar 14, 2022
Messages
9
Office Version
  1. 2013
Platform
  1. 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)


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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top