?
Solved

VBA copy only cells with value, no formulas

Posted on 2012-12-30
6
Medium Priority
?
366 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
0
Comment
Question by:mike637
6 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 1200 total points
ID: 38732024
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
0
 
LVL 14

Expert Comment

by:Faustulus
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.
http://msdn.microsoft.com/en-us/library/office/aa213567(v=office.11).aspx
0
 
LVL 50

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.

    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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50

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
        c.Copy
        Sheets("Sheet2").Range(c.Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
Next

Open in new window

0
 

Author Closing Comment

by:mike637
ID: 38741439
Thank you Experts.

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

Michael
0
 
LVL 50

Expert Comment

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

Marty - MVP 2009 to 2012
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

864 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