copy cell formula in Excel Macro

MrSmith
MrSmith used Ask the Experts™
on
I have a macro that copy information from a group of cells to another group of cells. It seems though that when I do the copy, Excel grabs the VALUE of the cell and not the FORMULA.

I can understand why, the cells being copied to might not have the same cell numbers and what not.

But the quesiton is, is there a way to copy just the formula and not the value?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MrSmith,
Could you, please, post the copy/paste code?

Range("A1:C10").Copy Range("F3")
should copy the range A1:C10 to F3:H12 ... and normally with formula+formats+...

Thanks,
Sébastien

Author

Commented:
Range("U11:V200,Y11:Y200,AA11:AA200,AJ11:AM200").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I am new to Macro writing, and now that I have posted this, I think I see where the mistake might be.

does Paste:=xlValues have to be changed?
MrSmith,
Yes, you can try with Paste:=xlFormulas
--> pastes only formula (no formats)

However, what is your destination range (paste range)?
In your code above, it shows it is the same than the copied-range. Is that it?
If so, it means the paste-range has multiple areas, therefore, the Paste or PasteSpecial shouldn't work.

What are you realy trying to accomplish?
-Copy Range("U11:V200,Y11:Y200,AA11:AA200,AJ11:AM200")
  or what the user has selected
-Paste it over the same range / or to a new location? where?

Regards,
Sébastien
   
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I have been given a macro that we have been using for over a year now to do some things. I am new to the working of this company and am not sure why they are using the macro in this way. They have realized that the macro is not takin the formula but only the value and have asked me to fix it because I know some thing about computers :-)

Here is the macro in its full form, I only pasted what I thought was applicable

Sub Copy_orig()

    Range("U11:V200,Y11:Y200,AA11:AA200").Select
    Range("AA11").Activate
    ActiveWindow.SmallScroll ToRight:=4
    Range("U11:V200,Y11:Y200,AA11:AA200,AJ11:AM200").Select
    Range("AJ200").Activate
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B3").Select
    Sheets("Sheet1").Select
    Range("Y11").Select
    Application.CutCopyMode = False
End Sub

Sub Copy_update()

    Range("U11:V200").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("U11:V200").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("L3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("O3").Select
    Sheets("Sheet1").Select
    Range("X180").Select
    Application.CutCopyMode = False
    ActiveWindow.LargeScroll Down:=-1
    Range("X149").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("X118").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("X87").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("X56").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("X25").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("Y11").Select
End Sub

Sub Return_orig()

    ActiveSheet.Unprotect
    Sheets("Sheet2").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("R134").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("R90").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("R46").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("R3:R200").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("Y11").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("Sheet2").Select
    Range("S3:S200").Select
    Range("S200").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("AA11").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=6
    Sheets("Sheet2").Select
    Range("T3:W200").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("AJ11").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("Y11").Select
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
One important thing (and maybe the why the old code was copying values only):
If in SHEET 1 cell D10 you have the formula =E10 (cell on right of D10)
   and you copy this cell with the formula to SHEET 2 A5
then
   SHEET2 A5 contains now the formula = B5
   ie, B5 on SHEET 2
   ie, it won't keep =E10 of SHEET1

Is that really what you want?
(i don't know why but i guess you want it to be =E10 of SHEET1)
Else, the copy/paste/pasteSpecial methods won't work and you'll have to take a completely different approach.

Please, let me know.

Thanks.
Sébastien
Anne TroyEast Coast Manager

Commented:
Hi, MrSmith! Kudos to learning VBA. I never touch the stuff myself. :)

I do know this, though:

When you record a macro and get lines like this:

   ActiveWindow.LargeScroll Down:=-1
   Range("X149").Select
   ActiveWindow.LargeScroll Down:=-1
   Range("X118").Select
   ActiveWindow.LargeScroll Down:=-1
   Range("X87").Select
   ActiveWindow.LargeScroll Down:=-1
   Range("X56").Select
   ActiveWindow.LargeScroll Down:=-1
   Range("X25").Select
   ActiveWindow.LargeScroll Down:=-1
   Range("Y11").Select

All of those lines in between can be deleted. Range("Y11").Select will take you to that range. All that scrolling and other selection lines are what you did in between and isn't needed because the end result is the same--you want to end up at Y11.

This is just one of the first and easiest methods of cleaning up your code. I use Word code a lot and often delete all the lines that say UmptySquat = False. Since I know that would be the setting anyway, I just remove it. (Like for a font and the line indicates that Bold = False when I'm not changing anything about a font at all.)
Top Expert 2006

Commented:
Hello MrSmith,

maybe not an answer but it could help you in trying to see what's going on in the code you posted

'--------------------------------------------------

Sub Copy_orig()
   'copy AJ200 from sheet1 to cell B3 on sheet2
   Sheets("Sheet1").Activate
   Range("AJ200").Select
   Selection.Copy
   Sheets("Sheet2").Activate
   Range("B3").Select
   Selection.PasteSpecial Paste:=xlValues, _
                          Operation:=xlNone, _
                          SkipBlanks:=False, _
                          Transpose:=False
   Sheets("Sheet1").Activate
   Application.CutCopyMode = False
End Sub

Sub Copy_update()
   'copy range U11:V200 from sheet1 to cell L3 on sheet2
   Sheets("Sheet1").Activate
   Range("U11:V200").Select
   Selection.Copy
   Sheets("Sheet2").Activate
   Range("L3").Select
   Selection.PasteSpecial Paste:=xlValues, _
                          Operation:=xlNone, _
                          SkipBlanks:=False, _
                          Transpose:=False
   Sheets("Sheet1").Activate
   Application.CutCopyMode = False
End Sub

Sub Return_orig()
   
   'Unprotect sheet1
   ActiveSheet.Unprotect
   
   'copy range R3:R200 from sheet2 to cell Y11 on sheet1
   Sheets("Sheet2").Activate
   Range("R3:R200").Select
   Selection.Copy
   Sheets("Sheet1").Activate
   Range("Y11").Select
   Selection.PasteSpecial Paste:=xlValues, _
                          Operation:=xlNone, _
                          SkipBlanks:=False, _
                          Transpose:=False
   
   'copy range S3:S200 from sheet2 to cell AA11 on sheet1
   Sheets("Sheet2").Activate
   Range("S3:S200").Select
   Selection.Copy
   Sheets("Sheet1").Activate
   Range("AA11").Select
   Selection.PasteSpecial Paste:=xlValues, _
                          Operation:=xlNone, _
                          SkipBlanks:=False, _
                          Transpose:=False
   
   'copy range T3:T200 from sheet2 to cell AJ11 on sheet1
   Sheets("Sheet2").Activate
   Range("T3:W200").Select
   Selection.Copy
   Sheets("Sheet1").Select
   Range("AJ11").Select
   Selection.PasteSpecial Paste:=xlValues, _
                          Operation:=xlNone, _
                          SkipBlanks:=False, _
                          Transpose:=False
   
   'Protect sheet1
   Application.CutCopyMode = False
   ActiveSheet.Protect DrawingObjects:=True, _
                        Contents:=True, _
                        Scenarios:=True
End Sub


'--------------------------------------------------

Some rewritten versions of what you posted it could be more simplified i guess but at least this is code without macro stuff that doesn't help to do what you intended

HAGD:O)Bruintje
Top Expert 2006
Commented:
in your VBA help look up the pastespecial method of the range hit the F1 in the vba editor and look for the method

this is from the help

Pastes a Range from the Clipboard into the specified range.

Syntax

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

expression   Required. An expression that returns a Range object.

Paste   Optional Variant. The part of the range to be pasted. Can be one of the following XlPasteType constants: xlPasteAll, xlPasteFormulas, xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The default value is xlPasteAll.

Operation   Optional Variant. The paste operation. Can be one of the following XlPasteSpecialOperation constants: xlPasteSpecialOperationNone
, xlPasteSpecialOperationAdd, xlPasteSpecialOperationSubtract, xlPasteSpecialOperationMultiply, or xlPasteSpecialOperationDivide. The default value is xlPasteSpecialOperationNone.

SkipBlanks   Optional Variant. True to have blank cells in the range on the Clipboard not be pasted into the destination range. The default value is False.

Transpose   Optional Variant. True to transpose rows and columns when the range is pasted.The default value is False.

and it will tell you the why of pasting with xlformulas instead of xlvalues, you can also paste more then once like in this snippet

Sub Copy_orig()
   'copy AJ200 from sheet1 to cell B3 on sheet2
   Sheets("Sheet1").Activate
   Range("AJ200").Select
   Selection.Copy
   Sheets("Sheet2").Activate
   Range("B3").Select
   
   'First the formulas
   Selection.PasteSpecial Paste:=xlFormulas
   'Second the formats
   Selection.PasteSpecial Paste:=xlFormats
   
   Sheets("Sheet1").Activate
   Application.CutCopyMode = False
End Sub

Author

Commented:
Thank you All!

Author

Commented:
Thank you all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial