Home

How do I copy data only from 1 sheet to another without copying over functions & formulas new sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-642602%22%20slang%3D%22en-US%22%3EHow%20do%20I%20copy%20data%20only%20from%201%20sheet%20to%20another%20without%20copying%20over%20functions%20%26amp%3B%20formulas%20new%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-642602%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20copy%20data%20from%20one%20sheet%20to%20another%20as%20the%20cells%20have%20corrupted.%26nbsp%3B%20I%20only%20want%20the%20data%20to%20copy%20in%20into%20the%20sheet%20without%20overwriting%20the%20new%20sheets%20functions%20and%20formulas%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-642602%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-642959%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20copy%20data%20only%20from%201%20sheet%20to%20another%20without%20copying%20over%20functions%20%26amp%3B%20formulas%20ne%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-642959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347833%22%20target%3D%22_blank%22%3E%40Suze61%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20suze%2C%3CBR%20%2F%3Euse%20the%20following%20macro%20from%20my%20vba-tanker.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20CopyOnlyData()%3CBR%20%2F%3EDim%20rngCell%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20Sheet1%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20rngCell%20In%20.UsedRange%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20rngCell.HasFormula%20%3D%20False%20Then%3CBR%20%2F%3E%3CBR%20%2F%3ESheet2.Range(rngCell.Address).Value%20%3D%20rngCell.Value%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20rngCell%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3CBR%20%2F%3EBernd%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Suze61
Occasional Visitor

I need to copy data from one sheet to another as the cells have corrupted.  I only want the data to copy in into the sheet without overwriting the new sheets functions and formulas ?

1 Reply

@Suze61 

Hi suze,
use the following macro from my vba-tanker.

 

Sub CopyOnlyData()
Dim rngCell As Range

With Sheet1

For Each rngCell In .UsedRange

If rngCell.HasFormula = False Then

Sheet2.Range(rngCell.Address).Value = rngCell.Value

End If

Next rngCell

End With

End Sub

 

Regards
Bernd
www.vba-tanker.com

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
203 Replies