Excel 2010 VBA copy the contents of one cell to multiple others

Ok can someone look at my code below and point me in the right direction. Forgive me I am no where near a expert at this.

Basically C is column number and i is row number and what I am trying to achieve is take the forumla in A1 of sheet and copy it to E6 to I6 then the value of A2 in sheet to E7 to I7 etc.

Please Login or Register  to view this content.

have you tried recording this as a macro? it should write the code for you. ie Developer>Record macro and then do the actions yourself ie Copy cell A1 highlight E6 to I6 and Paste

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi Munkle,

. There are infinite ways to do this. Before I explain PLEASE follow FDibbins request and re- name the Title. ****I would suggest something like “Copy Paste Method Formulas and Copy Paste Method Values” . Not only is that good for the reasons FDibbins said, but it could encourage more / better answerers and contribute to this Thread – As I said there are infinite ways to do this, I do not have time to suggest them all, but others may contribute..
.

. For Yous specific requirement:

. ( As pjwhitfield very rightly and correctly said the macro recorder is always a very good start point. In your case pay particular attention to the different options to paste in whilst recording the macro. )

Originally Posted by Munkle555

… …“…. take the formula in A1 of sheet and copy it to E6 to I6 then the value of A2 in sheet to E7 to I7 ……“..
…. If anyone does know whats wrong with the above though I would still like to know

. It appears a lot of basic concepts you have wrong. I have done one way for you, keeping close to your original code:

Starting with this

Using Excel 2007

Row\Col	A
1	=2*3
2	7

Sheet

Then this code :

Please Login or Register  to view this content.

Gives you this


Using Excel 2007

Row\Col	E	F	G	H	I
6	=2*3	=2*3	=2*3	=2*3	=2*3
7	7	7	7	7	7

Sheet

.. see some of my explain comments in the code.

Alan

***(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)EDit: As you have forfilled the request to re title, I shall continue!!!!

Hi Munkle,

. Thanks for correcting the title so quick. ( Please remember to PM FDibbins and tell’im )

. Here was a recent Thread with some other methods of Copying and pasting,
http://www.excelforum.com/excel-prog...ml#post4161312
.. although I think the Resize bit is the key to your requirement of copying the contents of one cell to multiple others

. That thread above was a bit more advanced, and allows you to Pick out easily parts of a table and copy that. However one method there, mine, was only copying values so would not have worked for copying your formula.
But the code is well commented to explain what is going on.
.
. It is with Googling as I suggest in my Signature below to find other similar Threads.

.... while I am here , another method, which rather than copies, per say, writes in the values from somewhere to somewhere else. This is limited again in exactly what you can copy , but you do have options such as .Values or .Formulas.
.
. Applied to your particular case try these lines tacked on just before the end of the code I did for you.

Please Login or Register  to view this content.

Again I think it is the Resize "Thing" ( Property as it is called ) that does the busines for you


Alan