The below code ran in 2:26 which is way too slow especially as it's only 1/3rd of the total (the remaining 2/3rd does something very similar just on different sections). Is there something I can do to speed this up? RangeCount = 625 and TimelineCount = 156 which are obviously large numbers (total is around 100k rows) but when RangeCount was 200 (30k rows) the code ran in 3 seconds...not sure why it's gone so slow now.
Link to sample: https://drive.google.com/file/d/1QHHtWad6_giWETZcAKw8-Qaq7Y6JJ4NX/view?usp=sharing
Edit: I believe I have discovered the issue - the table in Dash Data was an Excel table. I changed it from being an Excel table to just normal cells and it ran the whole code in 4 seconds. Can someone explain to me why the table is so slow or how I could improve the speed of it, please??
Sub copy_to_dash_data() Dim i, j As Integer Dim wsCalcs As Worksheet Dim wsDashData As Worksheet Dim LastRow As Long Dim LastCol As Long Dim FirstRow As Long Dim RangeCount As Long Dim TimelineCount As Long Dim LastCell As Long Dim PasteCell As Long Dim InvoiceDetailsColumnCount As Long Dim NamedRange As Range Dim StartTime As Double Dim TimeElapsed As String 'timer calculation StartTime = Timer 'set calcs to manual, disable user events and disable screen updating Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual 'setup sheet names for easy reference Set wsCalcs = ThisWorkbook.Sheets("Calcs") Set wsDashData = ThisWorkbook.Sheets("Dash Data") 'find last row and column of Dash Data and clear contents LastRow = wsDashData.Cells(Rows.Count, 1).End(xlUp).Row LastCol = Application.Match("P&L Amount", wsDashData.Rows(1), 0) wsDashData.Range(Cells(2, 1).Address, Cells(LastRow, LastCol).Address).ClearContents 'count the number of rows in the first Calc block and the timeline block FirstRow = wsCalcs.Range(Names("Invoice_Details")).Row LastRow = wsCalcs.Range(Names("Invoice_Details")).Rows.Count + FirstRow - 1 RangeCount = wsCalcs.Range(Names("Invoice_Details")).Rows.Count - 1 TimelineCount = wsCalcs.Range(Names("Months_and_Years_Timeline")).Columns.Count - 1 'month start, month end and year rows from Calcs values transfer to Dash Data a number of times equal to the number of rows in the Calc block (e.g. number of invoices) PasteCell = Application.Match("Month Start", wsDashData.Rows(1), 0) For i = 1 To RangeCount wsDashData.Range(Cells(2 + (TimelineCount * (i - 1)), PasteCell).Address, Cells(1 + (TimelineCount * i), PasteCell + 2).Address) = _ Application.Transpose(wsCalcs.Range(Names("Months_and_Years_Timeline"))) Next i 'enable events and screen updating Application.ScreenUpdating = True Application.EnableEvents = True Calculate 'code complete! TimeElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss") MsgBox ("Code complete in " & TimeElapsed), vbInformation End Sub