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:

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

<tbody>
</tbody>

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!