Worksheet Looping not working.

[kpark91]

What I'm trying to do here is to eliminate duplicates while copying the column A and column B from each worksheet in the same workbook.
The sheet1 worked fine but it wouldn't loop to the next worksheet.

If there is any way I can optimize my code, I would appreciate that too

Also, while I'm looping through the worksheets, is there a way that I could omit sheet11?
Would
If ws = sheet11 Then
exit for
End If
work?

Code: Select all

Sub Test()
    Dim counter As Integer
    counter = 1
    Dim ws As Worksheet
    Dim LR As Integer, temp1 As String
    Dim Dupl As Boolean
    
    Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Worksheets
        LR = ws.Range("A" & ws.Rows.count).End(xlUp).Row
        
        For c = 4 To LR
            temp1 = ws.Range("A" & c).Value
            Dupl = False
            For d = 1 To Sheet11.Range("A" & Sheet11.Rows.count).End(xlUp).Row
                If temp = Sheet11.Range("A" & d).Value Then
                    Dupl = True
                    Exit For
                End If
            Next d
            
            If Dupl = False Then
                Sheet11.Range("A" & counter).Value = temp1
                Sheet11.Range("B" & counter).Value = ws.Range("B" & c).Value
            End If
            
            counter = counter + 1
        Next c
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

[kpark91]

Also, sorry for the additional info but

Range("A" & Rows.Count).End(xlUp).Row method has caused a problem.

In my worksheets, I have spaces in between datas and that method can't seem to read all the data found in the worksheet due to blank spaces..
Is there another method where I could loop through all the used cells in column A?

[HansV]

Try this version; it should be much more efficient since it doesn't loop through the cells on Sheet11 to find duplicates, but uses CountIf instead:

Code: Select all

Sub Test()
    Dim counter As Long
    Dim ws As Worksheet
    Dim LR As Long
    Dim temp1 As String
    Dim c As Long
    Dim rng As Range

    Application.ScreenUpdating = False

    counter = 1
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> Sheet11.Name Then
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

            For c = 4 To LR
                temp1 = ws.Range("A" & c).Value
                If temp1 <> "" Then
                    If Application.WorksheetFunction.CountIf(Sheet11.Range("A:A"), temp1) = 0 Then
                        Sheet11.Range("A" & counter).Value = temp1
                        Sheet11.Range("B" & counter).Value = ws.Range("B" & c).Value
                        counter = counter + 1
                    End If
                End If

            Next c
        End If
    Next ws

    Application.ScreenUpdating = True
End Sub

[kpark91]

OMG!!!!! IT Works perfectly and much faster too!!!
RESPECT! :D

Thank you very much, Hans!

Do you have any idea why my worksheet loop wasn't working at all?

[HansV]

You apparently don't require explicit declaration of variables, otherwise the Visual Basic Editor would have warned you that the variable temp hasn't been declared. The line

If temp = Sheet11.Range("A" & d).Value Then

should have been

If temp1 = Sheet11.Range("A" & d).Value Then

See The importance of 'Option Explicit' for more info.

Furthermore, you increase the value of the variable counter whether you have copied data or not, this creates blank rows on the target sheet.

[kpark91]

Oh I see.
I read the linked post and it was a great help!
I always ommitted Option Explicit from VBA in order to use For Loop more easily but
I am convinced I should use Option Explicit from now on.

Thanks, Hans :)