Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Course Of The Month
3 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
VBA copy only cells with value, no formulas
Posted on 2012-12-30
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
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
[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
3
- +1
6 Comments
Active today
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
Regards
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
Regards
You might use ActiveSheet.Cells.SpecialC
http://msdn.microsoft.com/en-us/library/office/aa213567(v=office.11).aspx
http://msdn.microsoft.com/en-us/library/office/aa213567(v=office.11).aspx
Active today
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
Active today
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
Featured Post
Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
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 tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data.
Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Course of the Month3 days, 5 hours left to enroll
695 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.