Easy one (for you guys!)...code streamlining copy and paste

[dazfoz]

Afternoon all,

Im hoping that somebody can quickly help, Ive scoured the site for an answer to this somewhat easy question, but im struggling to find the answer.

I want to put the paste values into one line (Array?) then paste the copied cell, the VBA recorder output is as follows;

Range("F65").Select
                    ActiveCell.FormulaR1C1 = "=+R[-49]C-R[-2]C"
                    Range("F65").Select
                    Selection.Copy
                    Range("G65").Select
                    ActiveSheet.Paste
                    Range("J65").Select
                    ActiveSheet.Paste
                    Range("K65").Select
                    ActiveSheet.Paste
                    Range("N65").Select
                    ActiveSheet.Paste
                    Range("O65").Select
                    ActiveSheet.Paste
                    Range("R65").Select
                    ActiveSheet.Paste
                    Range("S65").Select
                    ActiveSheet.Paste
                    Range("AF65").Select
                    ActiveSheet.Paste
                    Range("AL65").Select
                    ActiveSheet.Paste
                    Range("H65").Select
                    Application.CutCopyMode = False
                    ActiveCell.FormulaR1C1 = "=+RC[-1]-RC[-2]"
                    Range("H65").Select
                    Selection.Copy
                    Range("L65").Select
                    ActiveSheet.Paste
                    Range("P65").Select
                    ActiveSheet.Paste
                    Range("T65").Select
                    ActiveSheet.Paste
                    Range("AM65").Select
                    ActiveSheet.Paste

I know I should be able to get this down to about 3 x lines of code but am having a blonde moment and cant get it to work! grrr

 

[hiker95]

dazfoz,

I think I got all the cells correct.

Here is a macro solution for you to consider.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub dazfoz()
' hiker95, 05/27/2015, ME857501
Range("F65,G65,J65,K65,K65,N65,O65,R65,S65").FormulaR1C1 = "=+R[-49]C-R[-2]C"
Range("AF65,AL65,H65,L65,P65,T65,AM65").FormulaR1C1 = "=+RC[-1]-RC[-2]"
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the dazfoz macro.

 

[dazfoz]

Brilliant,

I was putting "" around each cell in my range which was confusing me.

I like the .FormulaR1C1 = at the end of the range, much easier and quicker than copy & paste.

Thanks Hiker.

 

[hiker95]

dazfoz,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.