VBA copy only cells with value, no formulas

Posted on 2012-12-30
Medium Priority
Last Modified: 2013-01-03
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,
Question by:mike637
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 52

Accepted Solution

Rgonzo1971 earned 1200 total points
ID: 38732024

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

End Sub

Open in new window

LVL 14

Expert Comment

ID: 38732122
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.
LVL 49

Expert Comment

by:Martin Liss
ID: 38732863
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.

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Open in new window

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 800 total points
ID: 38732931
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
        Sheets("Sheet2").Range(c.Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If

Open in new window


Author Closing Comment

ID: 38741439
Thank you Experts.

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

LVL 49

Expert Comment

by:Martin Liss
ID: 38742134
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question