Copying value from different sheets

[nepotist]

Hello, I am trying to write a code to do a QC for my day to day work.
I have about 12 sheets of data, each representing different jurisdiction. I would like to check each row that contains data. the check being if the value in column J is less then Column K then copy range from A:C and columns J and K values to a different sheet say "QC".

I am stuck with a object error. Can some one please help me with this.
[VBA]Sub Quality_Check()

Dim i As Integer 'sheet index
Dim j As Integer 'Row Counter
Dim Lj As Integer 'Local counter
Dim Nr As Integer
j = 1
'Check for Number of Lanes
For i = 1 To 12 Step 1
With Worksheets(i).Activate

Nr = .Range("BD:BD").Rows.Count

For Lj = 10 To Nr Step 1
MsgBox Cells(Lj, 43).Value
MsgBox Cells(Lj, 35).Value
If (Cells(Lj, 43).Value < Cells(Lj, 35)) Then

With Worksheets("QC").Activate
.Cells(j, 2).Value = Worksheets(i).Cells(Lj, 2).Value
End With
j = j + 1
End If
Next Lj

End With

Next i


End Sub
[/VBA]

[GTO]

Hi there,

Both of these:
[vba]With Worksheets(i).Activate

Nr = .Range("BD:BD").Rows.Count [/vba]
[vba]With Worksheets("QC").Activate
.Cells(j, 2).Value = Worksheets(i).Cells(Lj, 2).Value[/vba]
...will fall over. .Activate is a Method and thus, has no properties (such as .Cells). Untested, try:

Sub Quality_Check()
Dim i As Long  'sheet index
Dim j As Long  'Row Counter
Dim Lj As Long  'Local counter
Dim Nr As Long
    j = 1
     
    Nr = Worksheets(1).Rows.Count
     'Check for Number of Lanes
    For i = 1 To 12 Step 1
        For Lj = 10 To Nr Step 1
            MsgBox Worksheets(i).Cells(Lj, 43).Value
            MsgBox Worksheets(i).Cells(Lj, 35).Value
            If (Worksheets(i).Cells(Lj, 43).Value < Worksheets(i).Cells(Lj, 35)) Then
                Worksheets("QC").Cells(j, 2).Value = Worksheets(i).Cells(Lj, 2).Value
                j = j + 1
            End If
        Next Lj
    Next i
End Sub

Hope that helps,

Mark

[nepotist]

GTO , I would like to determine the number of used row (rows with data) in each sheet before to make it run faster.
Also do you know how I can present the status (Like QC in process and done )

Also is there another way to increment the counter J= j +1 as i am seeing plenty of empty rows something like J++ AS IN C# PROGRAMMING LANGUAGE

[nepotist]

Ok I am done with most of this, This work book has more userdefined functions that I created as per the needs. Evey time I change a cell value it recalculated the entire sheet and which is slowing done the workbook.

How do I avoid this?.

[shrivallabha]

You can use enable / disable events while your macro runs like:
[VBA]Private Sub MyMacro()
'At the beginning of the code
Application.EnableEvents = False
'Main Code Finishes here
Application.EnableEvents = True
End Sub[/VBA]

[Chabu]

shrivallabha,
application.enableEvents will not prevent recalculation

What you need to do nepotist to prevent automatic recalculation is
[VBA]application.Calculation = xlCalculationManual[/VBA]
or one of the other two available constants

[Chabu]

Nepotist,

going back to the start of your request, it is always best to control quality while you are working instead of at the end of the day.
(Quality control versus Quality assessment (or audit)

So on top of doing the overview as described above, why don't you add a column in each sheet that checks the error.
(maybe even enhance it with some conditional formatting)

[shrivallabha]

Chabu,

Thanks for pointing that out (as he is dealing with UDFs and formulas and not some application/ worksheet based events). This reminds me that there's KB entry for this by Kenneth Hobson.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1035

[nepotist]

Chabu and Shrivallabha, thank you for your inputs.

Chabu, I have tried to set the calaculation to manual but it doesn't help. Also if you guys want to look at my code for all my UDF , please look at this thread
http://www.vbaexpress.com/forum/showthread.php?t=37503

Please provide your inputs for increasing the performance of the workbook and also if you help address the issue of the other thread.
Thank you

[nepotist]

Chabu,
We get DATA from other consultant and this QC makes few check and creates a summary of incorrect data information.