ExcelSOS21
New Member
- Joined
- Sep 29, 2021
- Messages
- 1
- Office Version
- 365
- 2010
- Platform
- Windows
- MacOS
Hello,
I have simple macro (see below) that works but it takes an unusually long time to process large data sets.
The macro runs between two worksheets within the same workbook.
In the main worksheet (Sheet3), the User selects a "taskname" (Sheet 3 cell d2) and then kicks off the macro to search (Sheet1) for all matches.
The macro then copies and paste the matches from Sheet1 to Sheet3.
I would appreciate any recommendations for cutting down processing time for large data sets.
I think I may need to use array, but I am not sure how to.
Thank you in advance for your help.
Sub finddata()
'1. declare variables
Dim taskname As String
Dim finalrow As Integer
Dim i As Integer
'2. clear old search results
Sheet3.Range("B6:J40").ClearContents
'3. find records that match criteria and paste them in Sheet3
taskname = Sheet3.Range("d2").Value
finalrow = Sheet1.Range("A1000").End(xlUp).Row
For i = 2 To finalrow
If Sheet1.Cells(i, 1) Like taskname Then
Sheet1.Activate
ActiveSheet.Range(Cells(i, 2), Cells(i, 10)).Copy
Sheet3.Range("B100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
Application.Goto (ActiveWorkbook.Sheets("Sheet3").Range("d2"))
End Sub
I have simple macro (see below) that works but it takes an unusually long time to process large data sets.
The macro runs between two worksheets within the same workbook.
In the main worksheet (Sheet3), the User selects a "taskname" (Sheet 3 cell d2) and then kicks off the macro to search (Sheet1) for all matches.
The macro then copies and paste the matches from Sheet1 to Sheet3.
I would appreciate any recommendations for cutting down processing time for large data sets.
I think I may need to use array, but I am not sure how to.
Thank you in advance for your help.
Sub finddata()
'1. declare variables
Dim taskname As String
Dim finalrow As Integer
Dim i As Integer
'2. clear old search results
Sheet3.Range("B6:J40").ClearContents
'3. find records that match criteria and paste them in Sheet3
taskname = Sheet3.Range("d2").Value
finalrow = Sheet1.Range("A1000").End(xlUp).Row
For i = 2 To finalrow
If Sheet1.Cells(i, 1) Like taskname Then
Sheet1.Activate
ActiveSheet.Range(Cells(i, 2), Cells(i, 10)).Copy
Sheet3.Range("B100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
Application.Goto (ActiveWorkbook.Sheets("Sheet3").Range("d2"))
End Sub