asked on

VBA copy only cells with value, no formulas

Hello Experts,

I am feeling stupid - I need some help with something that is escaping me.

I want to copy all data from workbook 1, sheet 1, usedrange, to workbook2, sheet 1 -
however I want to only copy cells that contain an integer and skip any cell that contains a formula. All cells are locked and all integers are positive.
My usedrange = ("D2:AA60")

The reason I am doing this is because the sheets are identical - however, I have modified the formulas in workbook 2 compared to workbook 1 - and I do not want to overwrite them.

Thank you,
Michael

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Faustulus

You might use ActiveSheet.Cells.SpecialCells(xlCellTypeConstants) to identify cells that have values other than formulas in them. Refer to Microsoft MSDN Help for full syntax.
http://msdn.microsoft.com/en-us/library/office/aa213567(v=office.11).aspx

Martin Liss

Unless you really mean that if a cell contains "abc" or other text that you don't want to copy it then this should do what you want. Change the "Sheet2" destination to reflect the destination workbook and sheet.

    Range("D2:AA60").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D2:AA60").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Open in new window

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mike637

ASKER

Thank you Experts.

Your help was appreciated. My brain was not working at the time.

Michael

Martin Liss

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012