MS Excel VBA Macro.

Brass Contributor

Hi everyone

Here I make a VBA macro and want to take the values of the Remarks column from the sheet name "AS cs S" to any cell like G3 of the sheet name "AS Sanitary" but The error or can say the macro errors is when I run it works but the column "Remarks" of sheet name "AS cs S" will clear with the all rows values this is the main issue I asked for... but just I want to clearing the rows which is visible means showing the rows value "Ok" of column "Status" and  the other values will be stay on the row.... here some conditions I apply which are:


  1. Filter the "Status" column of the sheet name "AS cs S" by only the rows where "Ok" is placed.
  2. After "Status" column filtered then selecting both columns of "Remarks" & "Status" for clearing contents of "Ok" related rows.
  3. After clearing then again filter the "Status" column by showing all rows with blanks.
  4. Then filter the "S/N" column as ascending.
  5. Then copy the "Remarks" column and past values to another sheet like sheet name "AS Sanitary" cell G3.

Please if any of you can help me or have a solution then plz reply...


the VBA code for Macro is:


Sub AS_Confirm_S()
' AS_Confirm_S Macro

ActiveSheet.ListObjects("AS_cs_S").Range.AutoFilter Field:=6, Criteria1:= _
ActiveSheet.ListObjects("AS_cs_S").Range.AutoFilter Field:=6
ActiveWorkbook.Worksheets("AS cs S").ListObjects("AS_cs_S").Sort.SortFields. _
ActiveWorkbook.Worksheets("AS cs S").ListObjects("AS_cs_S").Sort.SortFields. _
Add2 Key:=Range("AS_cs_S[[#All],[SN]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("AS cs S").ListObjects("AS_cs_S").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
Sheets("AS Sanitary").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("AS cs S").Select

End Sub





0 Replies