Dim lRow As Long
lRow = Range("A" & Rows.count).End(xlUp).Row
For i = 2 To lRow
Cells(i, 6) = Cells(i, 1) & Cells(i, 2) & Cells(i, 3) & Cells(i, 4) & Cells(i, 5)
Next i
Option Explicit
Sub Test1()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim rngVisible As Range
Dim fRng As Range
'AutoFiltering
Set Ws1 = Workbooks("Standard_List1.xls").Sheets("SL1-6")
Set Ws2 = Workbooks("Standard_List1.xls").Sheets("SCS_List")
Set fRng = Ws1.Cells(6, 1).CurrentRegion
fRng.AutoFilter
fRng.AutoFilter Field:=13, Criteria1:="YES"
On Error Resume Next
Set rngVisible = fRng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
'Now starting to copy the
Sheets("SCS_List").Range("B2:J1000").ClearContents
Sheets("Sl1-6").Select
Range("A5:A5").Select
Range("A5:A5", ActiveCell.End(xlDown)).Select
Selection.Copy
Sheets("SCS_List").Select
Range("B2:B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sub Test1()
Sheets("SCS_List").Range("B2:J1000").ClearContents
With Sheets("SL1-6")
With .UsedRange
.AutoFilter
.AutoFilter Field:=13, Criteria1:="YES"
End With
.Range(.Range("A5"), .Range("A5").End(xlDown)).SpecialCells(xlCellTypeVisible).Copy
Sheets("SCS_List").Range("B2").PasteSpecial Paste:=xlValues
End With
End Sub
Option Explicit
Sub Test1()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim rngVisible As Range
Dim fRng As Range
'AutoFiltering
Set Ws1 = Workbooks("Standard_List1.xls").Sheets("SL1-6")
Set Ws2 = Workbooks("Standard_List1.xls").Sheets("SCS_List")
Set fRng = Ws1.Cells(6, 1).CurrentRegion
fRng.AutoFilter
fRng.AutoFilter Field:=13, Criteria1:="YES"
On Error Resume Next
Set rngVisible = fRng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
'Now starting to copy
Sheets("SCS_List").Range("B2:J1000").ClearContents
Sheets("Sl1-6").Select
Range("A3:A3").Select
Range("A3:A3", ActiveCell.End(xlDown)).Select
Selection.Copy
Sheets("SCS_List").Select
Range("B2:B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sub Test1()
Dim lRow As Long
Sheets("SCS_List").Range("B2:J1000").ClearContents
With Sheets("SL1-6")
With .UsedRange
.AutoFilter
.AutoFilter Field:=13, Criteria1:="YES"
End With
.Range(.Range("A5"), .Range("D5").End(xlDown)).SpecialCells(xlCellTypeVisible).Copy
With Sheets("SCS_List")
.Range("B2").PasteSpecial Paste:=xlValues
lRow = .Range("B" & Rows.count).End(xlUp).Row
.Range("C2:C" & lRow).Clear
For i = 2 To lRow
.Cells(i, 4) = .Cells(i, 4) & .Cells(i, 5)
Next i
.Range("E2:EC" & lRow).Clear
End With
End With
Sheets("Sl1-6").Select
Range("C3:C3,D3:D3").Select
Range("C3:C3,D3:D3", ActiveCell.End(xlDown)).Select
Selection.Copy
Sheets("SCS_List").Select
Range("D2:D2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False