Macro for saving cell data to another sheet
I have data in sheet1 with full of formulas. I want a VB/Macro program to save result in another sheet in selected cells. I want to save results, not the formula. for example: I am having data in cell B5 as "=A1" and data in cell C5 as "=A2". When i try to save cell B5 & C5 in another sheet by a macro, it copies "REF!!" or error but if i copy data of cell A1, it copies perfectly. Doesnt it copies formulas? Someone help me how can i do this.
Very simple.
Where A1 holds the formula, A2 will become the result. You can also copy the formula then paste special then select values. to do this in macro you do:
Range("A2").value = Range("A1").value
range("A1:A5").Copy Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
attach please your code
I want to save P36:U36 result value from sheet1(Label) to sheet2(Records). In P36 i have set a formula as "=A1" and result is = 105. I want to save 105, not "=A1" . When i try to save with following code, it saves as =#REF!
The following macro saves data of one sheet to another sheet and when run again, it saves another set of data below previously saved data.
Sub Save() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("Label").Range("P36:U36") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("Records") Lr = DestSheet.Cells(Rows.Count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.Copy DestRange With Application .ScreenUpdating = True .EnableEvents = True End With End Sub
Last edited by Cutter; 07-25-2012 at 09:01 PM. Reason: Added code tags
dim lastrow as long lastrow = Sheets("Records").Cells(Rows.Count, "A").End(xlUp).Row sheets("Label").range("P36:U36").Copy Range(lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Originally Posted by GaidenFocus
dim lastrow as long lastrow = Sheets("Records").Cells(Rows.Count, "A").End(xlUp).Row sheets("Label").range("P36:U36").Copy Range(lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Where to add the above code? do I need to attach these codes to my existing code or i need to create a new macro with the above code lines? I tried above as standalone macro but it doesnt work.
Anyway, Thanks for help.
@ aknsms
Welcome to the forum.
Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.
Cutter I have something similar I think, in my case I am presently writing to a worksheet in my workbook. Currently I have two sheets an 'entry' form and a 'data' store. I want to put the 'data' store on a remote server and a new workbook, is there some code that could do that? I am a newbie, I had this code from an old workbook I am trying to adapt for my current project, thanks for the help
Darn, sorry just saw that i should not post code in the text, sorry..
---------- Post added at 08:44 AM ---------- Previous post was at 08:39 AM ----------
code for my last post, any help would be really appreciated.Sheets("entry").Select Range("E20").Select Application.CutCopyMode = False Selection.Copy Sheets("data").Select ActiveSheet.Paste ActiveCell.Offset(0, 1).Range("a1").Select
thanks to all who may help.
Last edited by skattie; 08-24-2012 at 10:41 AM. Reason: wrote code in the text body
Hello skattie, and welcome to the forum.
Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
yes sorry, i saw that after i posted, i have started another thread..
thanks
Sk
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks