Copying and Pasting Values Based on Other Cell Contents

[Hobolord]

Hello Friends!

To start this off, I have Windows 7 with Excel 2010.

I am working on a project in which I need a macro to assist me with this copying and pasting. For easier explanation, I have recreated some of the spreadsheet below:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Type[/TD]
[TD]Ref #[/TD]
[TD]Terms[/TD]
[TD]Date[/TD]
[TD]Current[/TD]
[TD]1-30[/TD]
[TD]31-60[/TD]
[TD]61-90[/TD]
[TD]over 90[/TD]
[TD]Total[/TD]
[TD]Ret[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]13ABC[/TD]
[TD]ABC Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]IN[/TD]
[TD]12345[/TD]
[TD]NET 30[/TD]
[TD]10/21/15[/TD]
[TD]0.00[/TD]
[TD]500.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]500.00[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CM[/TD]
[TD]12346[/TD]
[TD]NET 30[/TD]
[TD]10/24/15[/TD]
[TD]0.00[/TD]
[TD]-450.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]-450.00[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]13XYZ[/TD]
[TD]XYZ CORP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]IN[/TD]
[TD]12222[/TD]
[TD]NET 60[/TD]
[TD]8/11/15[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]400.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]400.00[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]IN[/TD]
[TD]12223[/TD]
[TD]NET 60[/TD]
[TD]8/12/15[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]300.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]300.00[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]IN[/TD]
[TD]12225[/TD]
[TD]NET 60[/TD]
[TD]8/14/15[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]100.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]100.00[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]CM[/TD]
[TD]12227[/TD]
[TD]NET 60[/TD]
[TD]8/16/17[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]-50.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]-50.00[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]13LOU[/TD]
[TD]LOU CORP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]IN[/TD]
[TD]13456[/TD]
[TD]NET 15[/TD]
[TD]7/6/14[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]600.00[/TD]
[TD]600.00[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]13LOL[/TD]
[TD]LOL LLC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]CM[/TD]
[TD]14567[/TD]
[TD]NET 90[/TD]
[TD]11/1/15[/TD]
[TD]-100.50[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]-100.50[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So what I would like the macro to do is copy the Company number and name from columns A & B and paste them to the end of the applicable rows beneath. So The macro would copy A2 & B2 and paste them into L3 & M3 and L4 & M4, then copy cells A5 and B5 and paste them to the end of rows 6-9.

These are dynamic ranges, as not every customer will have the same amount of invoices/memos.

Any assistance with this would be greatly appreciated, as it takes me forever to do manually!

Thank you,

Hobo

 

[CodeNinja]

Why use a macro? Just put =A2 in cell L3 and copy it over and down...

If you want to, you can highlight L:M and copy and paste values so there is no formula...

If you MUST do this via macro (and I can't understand why)...

Sub copyOver()
Range("A2:B" & ActiveSheet.UsedRange.Rows.Count).Copy
Range("L3").Select
Selection.PasteSpecial

End Sub

Good luck,

CN.

 

[Hobolord]

Hello Code Ninja,

Thank you for your response.

The reason that I cannot use your solution is that using =A2 and copying it down does not put the customer name and number in every row. It only puts those in the row directly under the rows that have the customer number and name. In every other row, your solution would copy over the "IN" or "PA" or "CM" and the reference number.

This is why I asked for a macro, because it needs to copy and paste based on values, and a simple autofill formula will not work. Maybe if the formula specified that it should only = the last cell in column a that started with a number. But I don't know how to do that which is why I am here.

Thoughts?

 

[CodeNinja]

Ah, I think I understand what you want now...

Try this formula in cell L3 copied across and down:

=IF(ISBLANK(C2),A2,IF(ISBLANK(C3),"",L2))

 

[Hobolord]

Brilliant!

I modified it slightly to:

=IF(ISBLANK($C2),A2,IF(ISBLANK($C3),"",L2))

Works like a dream, thanks for your help!