VBA copy only cells with value, no formulas

mike637
mike637 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Top Expert 2016
Commented:
Hi,

You could use this function to determine formula

Function IsFormula(rng As Range)
If rng.HasFormula Then IsFormula = True
End Function

Looping through the cells like 
Sub DoTheJob()
Dim c As Range
Set MyUsedRange = Range("D2:d6")
For Each c In MyUsedRange
   If c.HasFormula = False Then
      c.Copy Destination:=Worksheets("Sheet2").Range(c.Address)
   End If
Next

End Sub

Open in new window


Regards
Faustulus

Commented:
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 LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Learn More!
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
And if you do want to skip non-numerics you can do this.


Dim c As Range

For Each c In Sheets("Sheet1").Range("D2:AA60")
    If IsNumeric(c) Then
        c.Copy
        Sheets("Sheet2").Range(c.Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
Next

Open in new window

mike637

Author

Commented:
Thank you Experts.

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

Michael
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

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

Marty - MVP 2009 to 2012

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