justdriving
09-24-2011, 03:53 PM
After opening attached excel file, I was expecting output to include Columns as given in Yellow Region. But, Output included all irrelevant columns, as shown in Green Column. How can it include only 5 Columns as displayed in yellow column of attachment.
Private Sub AiM()
Dim lastRowA, lastColAi, lastRowAi, lastColAim As Long
With Sheets(1)
.Activate
lastRowA = .Cells(.Rows.Count, 1).End(xlUp).Row
lastColAi = .Cells(1, 1).End(xlToRight).Column
lastRowAi = .Cells(.Rows.Count, lastColAi + 5).End(xlUp).Row
With .Range("A1")
.AutoFilter
.AutoFilter Field:=5, Criteria1:="S1"
.AutoFilter Field:=10, Criteria1:="=Yes", Operator:=xlOr, Criteria2:="="
.Resize(lastRowA, 10).Copy
.Offset(lastRowAi + 2, lastColAi + 4).Value = "Pending"
.Offset(lastRowAi + 2, lastColAi + 4).PasteSpecial Paste:=xlPasteFormats
.Offset(lastRowAi + 4, lastColAi + 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.AutoFilter
End With
lastColAim = .Cells(lastRowAi + 5, Columns.Count).End(xlToLeft).Column
.Range(Cells(lastRowAi + 3, lastColAi + 5), Cells(lastRowAi + 3, lastColAim)).Merge
.Range(Cells(lastRowAi + 3, lastColAi + 5), Cells(lastRowAi + 3, lastColAim)).Font.Size = 15
.Range(Cells(lastRowAi + 3, lastColAi + 5), Cells(lastRowAi + 3, lastColAim)).Font.Bold = True
.Range(Cells(lastRowAi + 3, lastColAi + 5), Cells(lastRowAi + 3, lastColAim)).Font.ColorIndex = 6
.Cells.Select
.Cells.EntireRow.AutoFit
End With
Application.CutCopyMode = False
End Sub
Private Sub AiM()
Dim lastRowA, lastColAi, lastRowAi, lastColAim As Long
With Sheets(1)
.Activate
lastRowA = .Cells(.Rows.Count, 1).End(xlUp).Row
lastColAi = .Cells(1, 1).End(xlToRight).Column
lastRowAi = .Cells(.Rows.Count, lastColAi + 5).End(xlUp).Row
With .Range("A1")
.AutoFilter
.AutoFilter Field:=5, Criteria1:="S1"
.AutoFilter Field:=10, Criteria1:="=Yes", Operator:=xlOr, Criteria2:="="
.Resize(lastRowA, 10).Copy
.Offset(lastRowAi + 2, lastColAi + 4).Value = "Pending"
.Offset(lastRowAi + 2, lastColAi + 4).PasteSpecial Paste:=xlPasteFormats
.Offset(lastRowAi + 4, lastColAi + 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.AutoFilter
End With
lastColAim = .Cells(lastRowAi + 5, Columns.Count).End(xlToLeft).Column
.Range(Cells(lastRowAi + 3, lastColAi + 5), Cells(lastRowAi + 3, lastColAim)).Merge
.Range(Cells(lastRowAi + 3, lastColAi + 5), Cells(lastRowAi + 3, lastColAim)).Font.Size = 15
.Range(Cells(lastRowAi + 3, lastColAi + 5), Cells(lastRowAi + 3, lastColAim)).Font.Bold = True
.Range(Cells(lastRowAi + 3, lastColAi + 5), Cells(lastRowAi + 3, lastColAim)).Font.ColorIndex = 6
.Cells.Select
.Cells.EntireRow.AutoFit
End With
Application.CutCopyMode = False
End Sub