Conditional Copying of Cells to new Excel Sheet

[rajeevmakayiram]

Hi All,

I would like to do conditional copying of cells from one sheet to other sheet.

Eg: A Sheet Contains a list server inventory. In J column I have "Production" ,"Test", "Blanks", "DR". I just wanna filter only the Test and Blanks cells and copy the corresponding value from B column. Eg: If J2 is "Test" it should copy and paste the value of B2 to another excel file. Similary if J3 is Blank it should copy and paste the value of B3 to the next row. The Test and Blank corresponding vlues shoulb appended in the new excel. This should run as a loop for the entire J Column.

Please assist.

Regards
Rajeev

 

[andiemac]

I generally try to avoid loops when possible. This option will allow you to capture the data as you describe all at once. You will need to update the sheet names and destination range since that wasn't provided.

Sub Visible_Cells()
Dim LR As Long
Range("B2").End(xlDown).Select
LR = ActiveCell.Row
 
    Sheets("Sheet1").Activate
    Range("A:J").Select
    Selection.AutoFilter Field:=10, Criteria1:="=BLANK", Operator:=xlOr, _
        Criteria2:="=TEST"
    Range("B2:B" & LR).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
 
End Sub

 

[rajeevmakayiram]

Thanks a lot for your help Sir !!

I have modified my script like this (as per my req.)

Set objExcel = CreateObject("Excel.Application")
Sub Visible_Cells()
Dim LR
Range("G2").End(xlDown).Select
LR = ActiveCell.Row

Sheets("C:\Scripts\Data\Sheet1").Activate
Range("A:S").Select
Selection.AutoFilter Field:=18, Criteria1:="=Test", Operator:=xlOr
Range("F2:F" & LR).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("C:\Scripts\Data\Sheet2.xls").Select
ActiveSheet.Paste

End Sub


But I get Runtime Erro

Line No: 10
Char 28
Expected Statement Error.

Please advise.

Regards
Rajeev

 

[andiemac]

I see in your code that it appears you are only filtering for TEST, and not both TEST or BLANK. If this is the case then you do not need the:

", Operator:=xlOr"

a the end of your line:

Selection.AutoFilter Field:=18, Criteria1:="=Test

If you want to filter for two criteria then keep the OR section as below:

    Selection.AutoFilter Field:=10, Criteria1:="=BLANK", Operator:=xlOr, _
        Criteria2:="=TEST"

 

[rajeevmakayiram]

Thanks it worked