Copying and Pasting Values Based on Other Cell Contents

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
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:

ABCDEFGHIJKLM
1TypeRef #TermsDateCurrent1-3031-6061-90over 90TotalRet
213ABCABC Corp
3IN12345NET 3010/21/150.00500.000.000.000.00500.000.00
4CM12346NET 3010/24/150.00-450.000.000.000.00-450.000.00
513XYZXYZ CORP
6IN12222NET 608/11/150.000.00400.000.000.00400.000.00
7IN12223NET 608/12/150.000.00300.000.000.00300.000.00
8IN12225NET 608/14/150.000.00100.000.000.00100.000.00
9CM12227NET 608/16/170.000.00-50.000.000.00-50.000.00
1013LOULOU CORP
11IN13456NET 157/6/140.000.000.000.00600.00600.000.00
1213LOLLOL LLC
13CM14567NET 9011/1/15-100.500.000.000.000.00-100.500.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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
644
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)...

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

End Sub

Good luck,

CN.
 

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
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

Well-known Member
Joined
Feb 18, 2013
Messages
644
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

Board Regular
Joined
Sep 9, 2015
Messages
64
Brilliant!

I modified it slightly to:

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

Works like a dream, thanks for your help!
 

Forum statistics

Threads
1,186,146
Messages
5,956,195
Members
438,238
Latest member
a cow tent

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top