Solved

VBA copy only cells with value, no formulas

Posted on 2012-12-30

Microsoft Excel

2 solutions

Medium Priority

360 Views

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,
Michael

Comment

Question by:mike637

[X]

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

Join The Community

6 Comments

LVL 53

Accepted Solution

by:Rgonzo1971

Rgonzo1971 earned 1200 total points

ID: 387320242012-12-30

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

LVL 14

Expert Comment

by:Faustulus

ID: 387321222012-12-31

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

LVL 49

Expert Comment

by:Martin Liss

ID: 387328632012-12-31

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

LVL 49

Assisted Solution

by:Martin Liss

Martin Liss earned 800 total points

ID: 387329312012-12-31

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

Author Closing Comment

by:mike637

ID: 387414392013-01-03

Thank you Experts.

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

Michael

LVL 49

Expert Comment

by:Martin Liss

ID: 387421342013-01-03

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

Marty - MVP 2009 to 2012

Featured Post

Question has a verified solution.

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

Deleting Shapes - A Warning

Article by: Martin

This article describes a serious pitfall that can happen when deleting shapes using VBA.

A Game of Draw Poker

Article by: Martin

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.

Building Probability Models in Excel Part 5: Modeling an Investment Using Discrete Random Variables

Video by: Toby

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…

Building Probability Models in Excel Part 7: Modeling a Correlated Two-Fund Investment

Video by: Toby

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …