making code more Efficient !!! please help

[virgiliocabrera]

Hello,
I'm using this code to verify values, and copy cells from one sheet to another in the same workbook...... the code works fine. but is really slow and Inefficient.

can you help me make the code run better.


=================================================================
Sub CopyCells()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim j As Long
Dim i As Long
Dim c As Long
Dim c1 As Long
Dim c2 As Long
Dim c3 As Long
Dim lastrow1 As Long
Dim lastrow2 As Long

Set sh1 = Worksheets("Situacion Madera")
Set sh2 = Worksheets("Madera Pedida")

lastrow1 = 3
lastrow2 = sh2.Cells(Rows.Count, 3).End(xlUp).Row



For c = 2 To 563
c1 = c + 1
c2 = c + 2
c3 = c + 3
j = 3
For i = 5 To lastrow2


If sh1.Cells(2, c).Value <= sh2.Cells(i, 3).Value And _
sh1.Cells(2, c1).Value <= sh2.Cells(i, 4).Value And _
sh1.Cells(2, c2).Value <= sh2.Cells(i, 5).Value And _
sh1.Cells(2, c3).Value > 0 Then
j = j + 1
sh2.Select
Cells(i, 3).Select
Selection.Copy
sh1.Select
Cells(j, c).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
sh2.Select
Cells(i, 4).Select
Selection.Copy
sh1.Select
Cells(j, c1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
sh2.Select
Cells(i, 5).Select
Selection.Copy
sh1.Select
Cells(j, c2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
sh2.Select
Cells(i, 7).Select
Selection.Copy
sh1.Select
Cells(j, c3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next i
c = c + 10
Next c

End Sub
================================================================
Thanks
virgiliocabrera

[nO_OnE]

For starters get rid of the select statements, :
Instead of the cells().select. then selection.copy, you can simplify it by

cells().copy

And for the place you are pasting them:

sh1.cells().paste

Fill in the items within the (), but this should speed up your code a lot.

[virgiliocabrera]

Thanks NO_One this really worked !

but i hat to leave all of the "PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False " code because if i change it to "paste" it keeps giving me error.

[westconn1]

but i hat to leave all of the "PasteSpecial Paste:

but you don't have to select first

also it may help to break your long conditional statement into smaller bites
evaluate the most likely to fail first, then others are only required to be evaluated when the first evaluate true

[virgiliocabrera]

Thanks for the idea.... i'll look into it

Thanks

[Siddharth Rout]

Is this what you are trying?

Sub CopyCells()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim I As Long, J As Long, K As Long, L As Long, C As Long
    Dim lastrow1 As Long, lastrow2 As Long

    Set sh1 = Worksheets("Situacion Madera")
    Set sh2 = Worksheets("Madera Pedida")

    lastrow1 = 3
    lastrow2 = sh2.Cells(Rows.Count, 3).End(xlUp).Row

    For C = 2 To 563 Step 10
        J = 3
        For I = 5 To lastrow2
            If sh1.Cells(2, C).Value <= sh2.Cells(I, 3).Value And _
            sh1.Cells(2, C + 1).Value <= sh2.Cells(I, 4).Value And _
            sh1.Cells(2, C + 2).Value <= sh2.Cells(I, 5).Value And _
            sh1.Cells(2, C + 3).Value > 0 Then
                J = J + 1: L = C
                For K = 3 To 7
                    If K <> 6 Then
                        sh2.Cells(I, K).Copy
                        sh1.Cells(J, L).PasteSpecial Paste:=xlPasteValues, _
                        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                        L = L + 1
                    End If
                Next K
            End If
        Next I
    Next C
End Sub

[virgiliocabrera]

thanks very much koolsid !!! this also work's.... have to make minor adjustments to it works like a charm.... is more advance than my basic code (which i appreciate), remember... I just started one week ago!