Simple Copy Paste Macro Not Working!!!! :@ :@ :@

[HACZN12]

I'm trying to create a simple macro, its not working and therefore driving me a bit crazy, all i'm aiming to do is:

If the value in the T/F record is set to true in the table 1 (listed below):

Table 1
No.1	T/F	Phone number	Other random info	Contact	Percentage
1	TRUE	20453534	fdgeshsh	Random name	110%
2	FALSE	454354	eghertgesrg	Random name	120%
3	FALSE	24532534	regergrg	Random name	130%
4	TRUE	2435345	ergsar	Random name	140%
5	TRUE	234525	ergertghrt	Random name	150%
6	FALSE	425345	erhwrh	Random name	160%
7	FALSE	23452345	erhwrehrth	Random name	170%
8	TRUE	245254	htrhgfbgb	Random name	180%
9	FALSE	356364	dfbfbf	Random name	190%
10	TRUE	2452	efgethgetheth	Random name	200%

<tbody>
</tbody>

I would like only the contact and percentage to be pulled through into table 2 (outlined below) upon activating the macro:

Table 2
No.1	Contact	Percentage
1
2
3
4
5
6
7
8
9
10

<tbody>
</tbody>

Table 2 resides on a separate worksheet, any help would be very much appreciated!


Let me know if you require further information and i'll elloborate!

Regards

H

 

[Fluff]

How about

Sub CopyData()

   With Sheets("[COLOR=#ff0000]Summary[/COLOR]").Columns(2)
      .Replace False, "@", xlWhole, , False, , False, False
      With .SpecialCells(xlConstants, xlLogical)
         .Offset(, 3).Copy Sheets("[COLOR=#ff0000]New[/COLOR]").Range("B" & Rows.Count).End(xlUp).Offset(1)
         .Offset(, 4).Copy Sheets("[COLOR=#ff0000]New[/COLOR]").Range("C" & Rows.Count).End(xlUp).Offset(1)
      End With
      .Replace "@", False, xlWhole, , False, , False, False
   End With
End Sub

Changing sheets names in red to suit.

 

[HACZN12]

That worked perfectly! Thank you words cannot express how long i've been scratching my head at this one!!! what if i however wanted to apply two criteria i.e. pulling through values where lets say T/F is True and the "Other Random Info" says "Red"?

 

[Fluff]

Like this

Sub CopyData()

   With Sheets("Summary")
      .Columns(2).Replace False, "@", xlWhole, , False, , False, False
      .Columns(4).Replace "[COLOR=#ff0000]Red[/COLOR]", True, xlWhole, , False, , False, False
      With .Columns(2).SpecialCells(xlConstants, xlLogical)
         .Offset(, 3).Copy Sheets("New").Range("B" & Rows.Count).End(xlUp).Offset(1)
         .Offset(, 4).Copy Sheets("New").Range("C" & Rows.Count).End(xlUp).Offset(1)
      End With
      With .Columns(4).SpecialCells(xlConstants, xlLogical)
         .Offset(, 1).Copy Sheets("New").Range("B" & Rows.Count).End(xlUp).Offset(1)
         .Offset(, 2).Copy Sheets("New").Range("C" & Rows.Count).End(xlUp).Offset(1)
      End With
      .Columns(2).Replace "@", False, xlWhole, , False, , False, False
      .Columns(4).Replace True, "[COLOR=#ff0000]Red[/COLOR]", xlWhole, , False, , False, False
   End With
End Sub

Changing the word red for whatever word you want

 

[HACZN12]

Thank you! It was working great then when i tried run it this morning it said Run-time error '1004': No cells were found and the debugger points me to the third line of the script: With .SpecialCells(xlConstants, xlLogical)

Do you know what could be causing this?

 

[HACZN12]

Its fine i got it to work again!!!!! nice one mate that saved me a hell of a lot of time!

 

[Fluff]

Glad to help & thanks for the feedback