Forum Discussion
MS Excel VBA Macro.
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:
- Filter the "Status" column of the sheet name "AS cs S" by only the rows where "Ok" is placed.
- After "Status" column filtered then selecting both columns of "Remarks" & "Status" for clearing contents of "Ok" related rows.
- After clearing then again filter the "Status" column by showing all rows with blanks.
- Then filter the "S/N" column as ascending.
- 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:= _
"<>"
Range("AS_cs_S[[Remarks]:[Status]]").Select
Selection.ClearContents
ActiveSheet.ListObjects("AS_cs_S").Range.AutoFilter Field:=6
ActiveWorkbook.Worksheets("AS cs S").ListObjects("AS_cs_S").Sort.SortFields. _
Clear
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
.Apply
End With
Range("AS_cs_S[Remarks]").Select
Selection.Copy
Sheets("AS Sanitary").Select
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("AS cs S").Select
Range("K1").Select
End Sub