Send data from A to B by command only each row

[HelpHelpHelp]

Hello your Excellency

I hope this is simple enough. I need to be able to click a cell and it can send data from a specific area to another area.

I have attached a simple sheet.

Thank you.

[PCI]

Is it all rows from B to K to copy ?

[HelpHelpHelp]

Yes, B5 to K5 and all the rows that follow B6 to K6 etc
Thanks

[BadlySpelledBuoy]

Put the below in the worksheet module and activate it by double clicking a cell in column M.

Note the code covers only rows 5 to 55 but can easily be amended to cover more / less rows.

Please Login or Register  to view this content.

BSB

[HelpHelpHelp]

Sorry BSB, this is totally beyond me, looks like some VBA stuff. I only grasp the basic Excel. I was hoping its alot easier than this. I don't even know where to start with your codes Best leave this, thanks anyway.

[PCI]

If cells 'color is not an issue, why not to use a formula.
O6 =IF(B6="","",B6)
Then copy and paste to column X and down as needed

[BadlySpelledBuoy]

Sorry BSB, this is totally beyond me, looks like some VBA stuff...

You posted the question in the VBA section of the forum. I assumed you were expecting that as a solution.

I was hoping its alot easier than this. I don't even know where to start with your codes Best leave this, thanks anyway.

VBA is not the dark art many perceive it as. Follow the simple steps below:
1) Copy the code from post #4
2) Open your workbook
4) Right click on the Sheet1 tab and select 'View Code'
5) Paste the code you copied in step 1 into the code window that opens up
6) Close the code window
7) Enter some details in column A:K
8) Double click on the relevant row number in column M
9) Watch the data miraculously appear in columns O:X
10) Sit back and wonder why you've not learned to do this before and consider what other repetitive tasks you perform in Excel that could be automate

If none of that appeals to you then you can either go with the copy and paste approach suggested by PCI or you can use the attached where I've done all the 'difficult' work for you.

BSB

[HelpHelpHelp]

thanks PCI, but I do need that SEND command because I only want data send when double clicked. The post below by BSB did the trick for me. Thank you for your advice and help anyway, I really appreciate that.

[HelpHelpHelp]

Hi BSB, that was just absolutely magical. You have solved a major problem for me. I cannot thank you enough. SO THANK YOU, THANK YOU........n!

[BadlySpelledBuoy]

Very happy I could help

BSB

[HelpHelpHelp]

HI BSB
I have another question still relating to this issue. I understand the M5:M55 part that I can adjust. The target area in which I want the data to copy to is somewhere else, perhaps another sheet. I don't know how to change that. Copy to same sheet is fine if easier. I think its the target offset part I need to amend but its not as simple as the M5:M55 bit. Please help again, thanks.

[PCI]

the data to copy to is somewhere else

Where is it ? How do you want to speficy it ?

[HelpHelpHelp]

Where is it ? How do you want to speficy it ?

Hi PCI
If you look at the code from post #4 from BSM and relate this to his attachment, you will see the data in B5:K5 are sent to destination O5:X10. To adjust destination, I think (don't forget I know knowing about VBA) I need to change the Range and Target offset. I have tried a few times and it looked stange. If I want to send data to say, destination cell AA5:AJ5, what do I adjust?

Thanks

[BadlySpelledBuoy]

To update like that you need to adjust the column element of the OFFSETs.
.Offset(Rows, Columns)

Please Login or Register  to view this content.

BSB

[PCI]

OK clear
Change statement

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The reference is column M
-11 stands for 11 columns on the left from M it means B
-2 stands for 2 columns on the left from M it means K
So is collected data from B to K in the row where you clicked twice

11 stands for 11 columns on the right from M it means X
2 stands for 2 columns on the right from M it means O
So data collected previously is pasted from O to X in the same row

To get AA and AJ from destination use 14 and 23

[BadlySpelledBuoy]

To explain a little more, Target is the cell you double click in. You want to specify how many cells to offset from that and copy the data to.

So Target.Offset(0,10) will offset zero rows and ten columns from the target.
Target.Offset(5,20) will offset five rows down and 10 columns to the right.

So in your case you need to adjust the two red numbers to whatever number of columns you want the copied data to appear in FROM the target.

BSB

[HelpHelpHelp]

Thanks again BSB

[HelpHelpHelp]

Thanks PCI, now am getting the hang of this, I love the detail, now I can do more, thanks a million

[PCI]

To help you here a remake of BadlySpelledBuoy' code
Adjust DestCol to your need: Destination column

Please Login or Register  to view this content.

[HelpHelpHelp]

HI PCI, I've Just tried it, its MAGIC. I can't thank the two of you enough

[PCI]

Good news ... team work result!
Enjoy EXCEL

[HelpHelpHelp]

Sorry guys, am hitting problem. I've saved the work in Excel micro enabled template (also saved in micro enabled workbook). When I open the book again the send function doesn't work, the code is still in there. What am I doing wrong

[PCI]

Can you access the code (Code must be in the sheet's code) and go step by step into the code using key F8.

[HelpHelpHelp]

Hi PCI, sorry for the slow reply, I work long hours.
I view the code via right clicking the sheet page and then view code, the VBA screen pops up. I placed your code in and close the screen. Everything works great. I then save this in Macro-enabled and it still works fine. I close the workbook then open it again, thats when it doesn't work. Ive tried numerous times and have check that the code is still there, same thing happens with the code from BSB. I use Excel 2007 version. I don't understand your F8 key but have tried with curser in different parts, nothing happens. I hope you know what the problem is, thank you again.

[PCI]

Is the issue occures with the file attached ?
If not, there is something wrong with your file: Others macros ...
If Yes, there is something wrong with your computer
This file is working very nicely on my computer

[HelpHelpHelp]

Hi PCI
Just download your file, yes, nothing works so something wrong my end. I will try this on another PC and hope. Thanks a million, have a nice day

[PCI]

Good luck
Hope you will find