Snasui Excel Forum

Code: Select all

i = i + 1

Code: Select all

i = Val(rt.Offset(-1, -1)) + 1

Code: Select all

Dim i As Integer 'Number of item.

Code: Select all

i = i + 1

Code: Select all

rt.Offset(0, -1) = i

Code: Select all

rt.Offset(0, -1) = Val(rt.Offset(-1, -1)) + 1

Code: Select all

Private Sub btnTransfer_Click()
    Dim sh As Worksheet 'Each sheet in workbook
    Dim rAll As Range 'All range in each sheet
    Dim rs As Range 'Each cell in rAll
    Dim rt As Range 'Last cell in sheet "All"
    Dim rtAll As Range 'Booking Index in sheet "All"
    Dim i As Integer 'Number of item.
    For Each sh In Worksheets
        If sh.Name <> "All" Then
            Set rAll = sh.Range("B6", sh.Range("B" & Rows.Count).End(xlUp))
            For Each rs In rAll
                With Sheets("All")
                    Set rtAll = .Range("D8", .Range("D" & Rows.Count).End(xlUp))
                    Set rt = .Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
                End With
                If Application.CountIf(rtAll, rs) = 0 Then
                    i = i + 1
                    rs.Resize(1, 5).Copy
                    rt.PasteSpecial xlPasteValues
                    rt.Offset(0, -1) = i
                End If
            Next rs
        End If
    Next sh
End Sub

Code: Select all

Sub RetriveBkIndex()
    Dim sh As Worksheet 'Each sheet in workbook
    Dim rAll As Range 'All range in each sheet
    Dim rs As Range 'Each cell in rAll
    Dim rt As Range 'Last cell in sheet "All"
    Dim rtAll As Range 'Booking Index in sheet "All"
    For Each sh In Worksheets
        If sh.Name <> "All" Then
            Set rAll = sh.Range("C6", sh.Range("C" & Rows.Count).End(xlUp))
            For Each rs In rAll
                With Sheets("All")
                    Set rtAll = .Range("D8", .Range("D" & Rows.Count).End(xlUp))
                    Set rt = .Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
                End With
                If Application.CountIf(rtAll, rs) = 0 Then rt = rs.Value
            Next rs
        End If
    Next sh
End Sub