Conditional Copy/Paste

[EwaWieja]

Hello, would someone give me some guidance on this?
I need a macro to reorganise some data:
I work only with one sheet ('Sheet1')
Column AE (Phone_Type_1) is a list of either 'Phone', 'Email', 'Landline' or 'Mobile'.
Column AF (Phone_number_1) contains the actual email addresses and phone numbers.
At the moment I have two columns, one with phone type and another with phone number, I need 3 columns with phone types (mobile, landline, email - as headers) with phone values in rows.

I've crated part of the code to add new columns with headers phone, mobile, landline etc and now I need to do the conditional copy & paste.
e.g. if phone type (column AE) = 'email' copy and paste to column AC
if phone type (column AE) = 'Mobile' copy and paste to column AA
I've written simple macros before, but this one (even with the help of examples I've found) is a little beyond me.
Any help would be appreciated.
Thanks.

 

[jasonb75]

Why not start with the macro recorder?

Filter column AE by Email.
Press Ctrl + g, then click 'Special', followed by 'Visible cells only', then 'OK'.
Copy the selected data, and paste where needed.

That will give you some code to play with, when I was starting, I found it easier by doing that than trying to follow code written by somebody else.

 

[EwaWieja]

Hi jasonb75, looks like a good idea. I've given it a go but don't seem to be able to paste values... And it looks like there is still quite a lot of manipulation needed with the code

 

[jasonb75]

Just to clarify, the existing data is produced by formula and you want to copy and pastespecial values?

 

[EwaWieja]

There is no formulas - after filtering the info like you suggested, I'm not able to paste it..would work with sort I suppose...?

 

[jasonb75]

I just realised the error in my suggestion.

Because you're pasting in the same sheet and need to remove the filter, it causes excel to empty the clipboard.

After copying, should we paste to the same row as the original entry to keep it in line with other detail, or should it be moved up to remove blank rows?

 

[EwaWieja]

I need to keep the numbers in the same row (one row is one constituent)

 

[jasonb75]

See if this works, it's the simplest method I could think of. It requires the headers of the new columns to be in place, "Mobile" in AA1, "Landline" in AB1, "Email" in AC1

Sub copystuff()
Dim rng As Range
Set rng = Intersect(Columns("AA:AC"), ActiveSheet.UsedRange)
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1, 3)
rng.FormulaR1C1 = "=IF(RC31=R1C,RC32,"""")"
rng.Value = rng.Value
End Sub

 

[EwaWieja]

got v excited about this... but unfortunately this is not working for me...

 

[jasonb75]

Is it doing anything at all?

Based on your layout description from your first post, it should work (I've used Columns AE and AF for the original data and AA:AC for the new data.

A couple of thoughts for reasons it might fail.

You said about inserting new columns to take the copied data, had that already been done, or did inserting the new columns move the original data from columns AE:AF to AH:AI?

Are your descriptions in the first column consistent, and the same as your headings on the new columns? To give one example, e-mail and email will not match.