Selecting One Cell Copies Range

Hi,

Question 1) Is there a way that whenever I select Cell B39 that it will copy cells B39:V39 ? From there I can manually paste that selection to where I need to.

If this is possible, it would save the effort of having to select B39:V39 manually and clicking CTRL-C. I am gathering data and the copying and pasting is killing me.



Question 2) Is it possible that when I click on Cell A9 that it can paste any data that I just copied from another worksheet to cells A9:D29? The size of the selection that I would copy is the same size as A9:D29 (4 columns & 29 rows).



I have searched for a solution, but I am not having much luck since I am anaware of the proper terminology for these types of actions.

Thank you.

Hi, and welcome to the forum,


You could use the following code in the Worksheet Selection_Change event to achieve both objectives.

Please Login or Register  to view this content.

HTH

WOW !

You guys Rock!


For Pasting
I tried all three of them and they all work very well. I am soooooo happy!
I added the On Error Resume Next, because I was getting an error if i didn't have anything to paste.


For Copying

I had some trouble at first. When I would paste the contents of B39, all I would get were $0.00 $0.00 . . .etc.

When I select Paste Special > Values and number formats It does paste the results that I wanted.


I do several copy and pastes from 3 different workbooks so this is going to work very well.


Question 1

For one of the workbooks, can I adapt. . .

Please Login or Register  to view this content.

. . .so that it also does the Paste Special > Values and number formats steps as well?



Question 2
I have a workbook with a very long list that is contained in 5 columns (A B C D E). I copy a section from column A to column D that is always 21 rows deep. I then paste that to another workbook using your fine code from above. I then come back to the original workbook and copy a different section from columns A to D. (Always Copy A to D and 21 rows deep).

Can I add code to this worksheet so that when I click on any cell in column A -it will then copy the data from that point over to column D and down 21 rows?

If that can be done - then the I will not have to worry about getting carpel tunnel.
The process will be as easy as click, click, click.

Thanks so much for your help. I am very pleased with what I can do already.

Glad we could help. I realise I didn't use the ranges you'd originally specified, (I'm always doing that when testing these things!), but I guess you sorted it out.

One other point. It's OK to use On Error Resume Next, but it needs to be done with care, otherwise you could end up with unexpected results. As soon as you're past the bit of code where you want to avoid an Application error, reset it with On Error GoTo 0 , so that any subsequent error will be flagged up.

Rgds

Hi,

Q1. Unless it's changed in Excel 2007, I've never found a quicker way than performing two pastes. i.e.

Please Login or Register  to view this content.

Q2. Use:

Please Login or Register  to view this content.

HTH

OK, understood.

Use:

Please Login or Register  to view this content.

Thank you.

Richard and VBA Noob both of your suggestions work just grand!

Thanks again!