I'm sure this is probably out there but I can't seem to find it.UpdatesI have tested some different code, and reduced my time to complete the task from 50/60 seconds to 10 seconds. This is what I changed:For k = 0 To 28    For v = 1 To countryLastRow(k)        If Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range(C6) Then             Sheet3.Range(Sheet3.Cells(v + 3, countryRanges(k)), Sheet3.Cells(v + 3, countryRanges(k) + 6)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0)        End If    Next vNext kUsing the range to copy and paste whole rows at once rather than 1 cell at a time has drastically improved my macro.Still looking out their for any more performance increases people know with the code I have but this seems to be good now.Basic rundownIn Excel I have multiple rows and multiple columns that need to be searched through then depending on criteria, copy it and paste it into another tab using VBA.I want to find the fastest/most efficient way of coding this search/copy/paste function.What I've TriedSo I went for a simple brute force code where I search through all cells in the Sheet2 table columns, use if formula to match the criteria then .copy destination:= to copy the row into the next empty row of table in Sheet1 then move onto the next Sheet2 table and repeat until all 29 tables have been searched.Quite quickly I found I needed to improve the speed of this and I added the following:Application.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualApplication.EnableEvents = Falsewhich improved the speed when I was only using one table. Since adding the second table it starting to become slower as 2148 rows were added.Now I am only using 2 tables worth of data (first table with 680 rows, second table with 2148 rows) and when running a wider search criteria rather than very specific search it takes around 30 seconds to complete. Considering this is only 2 tables, I believe that when all 29 tables are added it could take 10+ minutes to complete since these two tables are quite small compared to others (Biggest table will have around 8000 rows).Below is an example of the tables. Please see the example excel below for a better view of more rows.SKU, Count and Size aren't filled in since these are unimportant to the search, just copy and pasted where the Manufacturer, Brand, Sub Brand and Flavour match.This is a snippet of the search function:For k = 0 To 28    For v = 1 To countryLastRow(k)        If Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range(C6) Then             Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) - 1)          'Manufacturer             Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k))          'Brand             Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 1)      'SubBrand             Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 2)      'Flavour             Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 3)      'SKU             Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 4)      'Count             Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 5)      'Size        End If    Next vNext kwhereK = 0 to 28 scrolls through the country table columnsv = 1 to countryLastRow(k) scrolls through the rows for the country tablecountryLastRow(k) is the number of rows in the table on sheet3 so it won't just search through loads of blanks if the max number is more than the current table contains.countryRanges(k) is an array holding the starting column number for each tableThis snippet of code is 1 of 16 versions as there are multiple ways to define the criteria (used in a select case function) where it finds all cells in column 1 of a country table which matches the criteria of a ComboBox box on Sheet1 and the rest copy/pastes it.If the ComboBox on Sheet1 said WEETABIX LTD, All Brands, All Sub Brands, All flavours then it would copy both WEETABIX rows from AE Table and both WEETABIX rows from BE table leaving out the Kelloggs rows.I tried to change the way my search worked, attempted to only look in 1 column at a time rather than all rows in 1 column (for table1), next row then when ended going to next table however this was actually slower by a few seconds when I timed it.I've got an idea to do some data manipulation upon entering the data into tables (which uses a code to extract data from external Excel workbooks. So it is all alphabetical and find the first cell and last cell with the criteria and do a .range.copy .pastespecial but will take a while to write the code to do this as it will require multiple levels of sorting.Another Idea, where the condition is true, get the column1 value and .copy .pastespecial of the range using .offset to get the whole row rather than .copy destination each cell in the rowFull Code and excel table example links belowExample table in excel link here:http://www.filedropper.com/showdownload.php/example_6Full code in pastebin here:http://pastebin.com/WpeE84fXIf you want access to the full document let me know and I will send it privately.
Excel: Vba Copy and paste efficiency with 000's of rows and columns
excel;vba