Solved Keeping Cell Data Once Transferred To Another Sheet By Macro

July 7, 2012 at 06:18:45
Specs: Windows XP
Does anyone know how to keep formulas in cells. I am making a calculator of sorts that is using data in various cells within the same worksheet.

Once all the data has been entered into each cell I am using a macro to add all the entered fields from this front calculator page onto a new worksheet within the workbook, but I would like all the formulas that I used in each of the fields on the front calculator worksheet to remain in their respective cells once the data has been added to the saved worksheet, as at present it clears all formulas each time I use the macro to add the data to the following sheet.

This might be really simple but I'm learning Excel through trial and error at the moment so any help on that front would be much appreciated!

Just as an example:

In Cell B5 the formula is =B4/B3*12 when the data is added to another worksheet the calculator clears Cell B5 of the above formula, how do I force the calculator to keep the formula in the cell but still transfer that specific data from the calculation onto the other worksheet.

If the macro details are needed then I can provide them as not sure if that is clearing them rather than transferring the data.

See More: Keeping Cell Data Once Transferred To Another Sheet By Macro

July 7, 2012 at 08:34:44
There's no way we can troubleshoot a macro related problem without seeing the code.

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 8, 2012 at 00:33:14
Here is the macro. From looking at it is would seem that .ClearContents will remove the contents from the boxes? This is a rehash of another excel calculator so not entirely sure how it has been done.

Sub Button2_Click()
Dim NewRow As Integer
NewRow = Worksheets("Calculator").Range("J9").Value + 1
If Worksheets("Calculator").Range("C1").Value <> 0 Then
MsgBox "There are errors. No data has been added!", vbOKOnly, "Error"
Exit Sub
End If

Worksheets("Data Sheet").Cells(NewRow, 1).Value = Worksheets("Calculator").Range("B1").Value
Worksheets("Data Sheet").Cells(NewRow, 2).Value = Worksheets("Calculator").Range("B2").Value
Worksheets("Data Sheet").Cells(NewRow, 3).Value = Worksheets("Calculator").Range("B3").Value
Worksheets("Data Sheet").Cells(NewRow, 4).Value = Worksheets("Calculator").Range("B4").Value
Worksheets("Data Sheet").Cells(NewRow, 5).Value = Worksheets("Calculator").Range("B5").Value
Worksheets("Data Sheet").Cells(NewRow, 6).Value = Worksheets("Calculator").Range("B6").Value
Worksheets("Data Sheet").Cells(NewRow, 7).Value = Worksheets("Calculator").Range("B8").Value
Worksheets("Data Sheet").Cells(NewRow, 8).Value = Worksheets("Calculator").Range("D8").Value
Worksheets("Data Sheet").Cells(NewRow, 9).Value = Worksheets("Calculator").Range("B10").Value
Worksheets("Data Sheet").Cells(NewRow, 10).Value = Worksheets("Calculator").Range("B12").Value
Worksheets("Data Sheet").Cells(NewRow, 11).Value = Worksheets("Calculator").Range("B16").Value
If Worksheets("Calculator").Range("B7").Value = "Q" Then
Worksheets("Data Sheet").Cells(NewRow, 12).Value = Worksheets("Calculator").Range("B18").Value
Worksheets("Data Sheet").Cells(NewRow, 12).Value = Worksheets("Calculator").Range("B14").Value
End If
MsgBox "New Data added", vbOKOnly, "Complete"
Worksheets("Calculator").Range("J9").Value = NewRow
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Report •

July 8, 2012 at 02:18:35
✔ Best Answer
Did you happen to notice all of lines that end in .ClearContents?

Guess what they are doing?

You can delete those if you want or you can comment out them as a test first.

Put a single quote ' in front of each line and when you click off of it, it should turn green. Once it does, it will be considered a comment and not be executed.

BTW, the last 2 lines that you posted don't do anything.

Private Sub Selection_Change.....

End Sub

They are the framework (first and last line) for another macro but there are no instructions in between them. They can be deleted since they are just wasting resources.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question