Need Help with making copy and paste code faster

**Wes88** · 06-04-2012, 10:29 AM

Gentlemen

I am new to VBA and I have created some code that copys rows from one worksheet and pastes them to another. The code is working great it is just really slow. I was wondering if anyone could look at my code and make recommendations on how to speed it up.

Thank You for any help you can provide!

Sub CopyTypeI()
Dim Val As Integer
'Clears the sheeting
Sheets("TypeI").UsedRange.Clear

Val = Sheets("Database").UsedRange.Rows.Count

'Finds all ASTM Type I White Traffic Signs in the Database worksheet
For i = 1 To Val Step 1
If Sheets("Database").UsedRange.Cells(i, 17).Value = "Type I" And Sheets("Database").UsedRange.Cells(i, 8).Value = "White" Then
Sheets("Database").Rows(i).EntireRow.Copy Destination:=Sheets("TypeI").Range("B200000").End(xlUp).Offset(1, -1)
End If
Next



'Finds all ASTM Type I Green Traffic Signs in Database worksheet copys them and paste them into a column in the TypeI worksheet
For i = 1 To Val Step 1
If Sheets("Database").UsedRange.Cells(i, 17).Value = "Type I" And Sheets("Database").UsedRange.Cells(i, 8).Value = "Green" Then
Worksheets("Database").Rows(i).Range("A1:AX1").Copy Destination:=Worksheets("TypeI").Range("BB200000").End(xlUp).Offset(1, -1)
End If
Next
        


'Finds all ASTM Type I Yellow Traffic Signs in Database worksheet copys them and paste them into a column in the TypeI worksheet
For i = 1 To Val Step 1
If Sheets("Database").UsedRange.Cells(i, 17).Value = "Type I" And Sheets("Database").UsedRange.Cells(i, 8).Value = "Yellow" Then
Worksheets("Database").Rows(i).Range("A1:AX1").Copy Destination:=Worksheets("TypeI").Range("DC200000").End(xlUp).Offset(1, -1)
End If
Next
End Sub

**zbor** · 06-04-2012, 10:32 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**rkey** · 06-04-2012, 10:33 AM

You can always start with adding this:

sub main()
Application.ScreenUpdating = False
.....
Application.ScreenUpdating = True
end sub

**zbor** · 06-04-2012, 10:36 AM

rkey,
Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc