Sep 07 2022 05:43 AM
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:
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