1

I am currently working with 3 files: New file (Sht)- in which the macro is written and the other two are wsSource and wsDest. There are about 1000 rows in the new file and about 1000 rows in wsSource. LastRowAT is the last row of the New File. LastRowKS is the last row of wsDest. The code matches particular conditions and then based on these conditions, copies the data from wsSource to wsDest. Now, because I am using a for loop and nested if, sometimes excel crashes and has to restart. Can I optimize this piece of code in any manner so that it takes less time to run and more importantly does not crash? P.S This is just sample data. The real data would involve about 100,000 rows in the wsSource file 

For i = 2 To LastRowAT
    If sht.Range("B" & i).Value = "Khusbhu Singh" And sht.Range("D" & i).Value = "Allocated" And sht.Range("C" & i).Value = Max_date Then
        If sht.Range("A" & i).Value = Workbooks("CISF.xlsx").Worksheets("QUIDAM-INSURERS").Range("A" & i).Value Then
            Set rngCopy = wsSource.Range("A" & i & ":V" & i)
            rngCopy.Copy wsDest.Cells(LastRowKS, "I")
            wsDest.Cells(LastRowKS, "I").Resize( _
            rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
            wsDest.Range("A" & LastRowKS).Value = "Khusbhu Singh"
            wsDest.Range("B" & LastRowKS).Value = Max_date
            LastRowKS = LastRowKS + 1
        End If
    End If
Next i
Rithwik Sarma
  • 51
  • 1
  • 8
  • 1
    Can't you use the Macro, to pull all the sheets in a New Workbook and then do the stuff, and return the sheets. That would be much faster as, no sifting through multiple books will be required. – Mikku Jun 21 '19 at 06:08
  • Okay, I can pull all the sheets in the new workbook. But how do I then save the contents into the original respective workbooks? – Rithwik Sarma Jun 21 '19 at 06:29
  • Same way you are moving Those sheets in one workbook. Move them back into the Original workbooks. – Mikku Jun 21 '19 at 06:35
  • This might help you: https://stackoverflow.com/questions/55379925/performance-difference-between-checking-a-cells-interior-colour-vs-its-value/55381841#55381841 – Dean Jun 21 '19 at 07:22

0 Answers0