Sub SplitByAuctionType()
Dim shtItems As Worksheet
Dim shtSilent As Worksheet, shtLive As Worksheet
Dim sType As String
Dim rowLastItems As Long
Dim rngItems As Range
Dim arrItems As Variant, arrSilent As Variant, arrLive As Variant
Dim i As Long
Set shtItems = Worksheets("Items")
Set shtSilent = Worksheets("Silent")
Set shtLive = Worksheets("Live")
With shtItems
rowLastItems = .Range("A" & Rows.Count).End(xlUp).Row
Set rngItems = .Range(.Cells(2, "A"), .Cells(rowLastItems, "D"))
arrItems = rngItems.Value
End With
shtSilent.Range("A1").CurrentRegion.Offset(1).ClearContents
shtLive.Range("A1").CurrentRegion.Offset(1).ClearContents
ReDim arrSilent(1 To UBound(arrItems), 1 To 2)
ReDim arrLive(1 To UBound(arrItems), 1 To 2)
Dim iSilent As Long, iLive As Long
For i = 1 To UBound(arrItems)
sType = UCase(arrItems(i, 4))
Select Case sType
Case "S"
iSilent = iSilent + 1
arrSilent(iSilent, 1) = arrItems(i, 1)
arrSilent(iSilent, 2) = arrItems(i, 2)
Case "L"
iLive = iLive + 1
arrLive(iLive, 1) = arrItems(i, 1)
arrLive(iLive, 2) = arrItems(i, 2)
Case Else
' Do nothing
End Select
Next i
With shtSilent.Range("A2").Resize(iSilent, UBound(arrSilent, 2))
.Value = arrSilent
.EntireColumn.AutoFit
End With
With shtLive.Range("A2").Resize(iLive, UBound(arrLive, 2))
.Value = arrLive
.EntireColumn.AutoFit
End With
End Sub
Sub CopyToCheckOut()
Dim shtCheckOut As Worksheet
Dim shtSrc As Worksheet
Dim arrShtNames As Variant
Dim sType As String, arrType As Variant
Dim rowLastSrc As Long, rowNextCheckout As Long
Dim rngSrc As Range
Dim arrItems As Variant, arrSilent As Variant, arrLive As Variant
Dim i As Long
Set shtCheckOut = Worksheets("Check out")
arrShtNames = Array("Silent", "Live")
shtCheckOut.Range("A1").CurrentRegion.Offset(1).ClearContents
For i = 0 To UBound(arrShtNames)
With Worksheets(arrShtNames(i))
rowLastSrc = .Range("A" & Rows.Count).End(xlUp).Row
Set rngSrc = .Range(.Cells(2, "A"), .Cells(rowLastSrc, "E"))
End With
With shtCheckOut
rowNextCheckout = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & rowNextCheckout).Resize(rngSrc.Rows.Count, rngSrc.Columns.Count).Value = rngSrc.Value
End With
Next i
With shtCheckOut.Range("A1").CurrentRegion
.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
.EntireColumn.AutoFit
End With
End Sub
