Copy and paste to second Sheet (Conditional)

[Raph85]

Hi Excel experts,

I have a data that has a dept code on it on column A and I would like to copy only the data on dept code 1 to sheet 2.

Could you please kindly share your expertise on the VBA code for this method.

Thank you.

Raphael.

 

[Raph85]

Hi 'my answer is this',

Thank you so much for your help, the macros is worked perfectly and sorry for all the trouble as I'm still quite new to this forum.

Thanks

 

[My Aswer Is This]

Glad to see I was able to help you. I do notice your sort of new here and I'm sure you will see that questions asked with lots of details get you answers quicker.

If you want to delete the rows on sheet one after copying them just add the line of script to the new script you should see in my second posting. It's always good to try and read these scripts best you can that way you will get to a point where you can write great scripts yourself. Take care.

 

[Raph85]

Hi,

If I'm adding a few Sheet in front of Sheet 1, which instructions do I need to change as I tried to add a couple Sheet in before sheet 1 then it didn't copy any data to Sheet 2. I don't quite understand what is this instruction does " Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1"

how do I figure out the Sheet index number on Lastrow = Sheets(2).

Sorry for all the questions.

Thank you

 

[My Aswer Is This]

Try this:
You will notice I changed the sheet names "CopyFrom" and "CopyTo"
You can change these sheet names to what you like.
This script is the one that will delete the rows in sheet ("CopyFrom') after copying the rows to sheet "CopyTo"

Sub FilterMini()
'Mod 3
Dim Lastrow As Long
Lastrow = Sheets("[COLOR="#FF0000"]CopyTo[/COLOR]").Cells(Rows.Count, "A").End(xlUp).Row + 1
    With Worksheets("[COLOR="#FF0000"]CopyFrom[/COLOR]").Range("A1").CurrentRegion
        .AutoFilter Field:=1, Criteria1:="1", Operator:=xlFilterValues
        .SpecialCells(xlCellTypeVisible).Copy Worksheets("[COLOR="#FF0000"]CopyTo[/COLOR]").Range("A" & Lastrow)
        .SpecialCells(xlCellTypeVisible).Delete
    
    End With
ActiveSheet.AutoFilterMode = False

End Sub

 

[bestfxson1c]

hello
I've tried the code..
Come here please
Functions used above

Excel VBA Functions</pre>