VBA: Moving Specific Cells to New Sheet

[Chonst]

So I need help writing a code that will effectively move this data, ignoring column C and F in the source and put in the new sheet, in a different location. But it just puts it in the next available row, so in the example, as I add more Cool Guys, it wont overwrite my current list of Cool Guys, and then after it does that, I want it clear the contents of the source cells that were CoolGuys again ignoring column C and F.

Everything I've tried so far breaks the spreadsheet or repeats on the Boolean conditional functions and then nothing.

Thank you in advance.

 

[hiker95]

Everything I've tried so far breaks the spreadsheet or repeats on the Boolean conditional functions and then nothing.

Chonst,

You are posting pictures. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually?????

So that we can get it right on the first try:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Fluff]

Untested, but try

Sub FilterCopy()

   With Worksheets("All The Guys")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("B2:G2").AutoFilter 4, "Cool Guy"
      With Intersect(.Range("B:B,D:E,F:F"), .Range("B3:G" & .Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlVisible))
         .Copy Worksheets("Really Cool Guys").Range("C" & Rows.Count).End(xlUp).Offset(1)
         .ClearContents
      End With
      .AutoFilterMode = False
   End With

End Sub

 

[Chonst]

There was a little tweaking I had to do, but this worked perfectly!

I appreciate it so much!

 

[Fluff]

Glad to help & thanks for the feedback