Need more efficient VBA Code for copy paste

[EricB9]

I have a bunch of expense categories and am using the macro to check if the categories are in a certain range. If they are not in the range, I need to insert a row copying the formulas from the above row and add the category. The code I am using is:

RepeatCheck = IsNumeric(Application.Match(TempCategory, Range("Management:GenAdmin"), 0))


If RepeatCheck = False Then

ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormulas
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormulas
Application.CutCopyMode = False


ActiveCell.Offset(0, 1).Value = Isblank

ActiveCell.Offset(0, 2).Value = TempCategory

Else

End If

The code works but it is slow. I have a lot of expense categories to cycle through so getting this as streamlined as possible is important.

Thanks for your help

 

[yky]

Do you have the following two lines of code at the beginning of your code?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

And the following two lines of code before the end of your code?

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[EricB9]

Yes, I have both in and it is still much slower than I would like. I'm not sure if it is the copy paste portion slowing it down or the match.

 

[mole999]

how many rows below do you have, do they have formulas, maybe 90% of those could be turned to static numbers so no formulas update, evry push a row down will henge every reference

 

[EricB9]

The rows all have formulas that need to be copied into the new row when inserted. Once the code finishes there will be about 10-20 rows inserted.

 

[yky]

You can try copying only the range that has data, not the entire row. If the ActiveCell is the left-most cell, try this:

Range(ActiveCell, ActiveCell.Offset(0, 5000).End(xlToLeft)).Copy 'substitute 5000 with the column number of the right-most column

 

[mole999]

I've always used this since I found it

'By-passes the Clipboard
Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")
'Or, if only values are needed:
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
'Or, if only formulae are needed:
Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula

 

[EricB9]

Thanks. I think I've determined the part that is slowing the code down is searching to see if the category is already there or not. I'm currently using the following code for this:

Dim TempCategory As String
Dim RepeatCheck As Boolean


[COLOR=#333333]RepeatCheck = IsNumeric(Application.Match(TempCategory, Range("Management:GenAdmin"), 0))[/COLOR]

Is there a better way to do this than what I'm currently using?

 

[yky]

To eliminate any guessing, you can put a time stamp before each line of code. So, you know exactly how much time is spent on each line.