Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Easier method to capture & paste calculated value?

  Asked By: Navin    Date: Sep 21    Category: MS Office    Views: 843
  

I have a macro that does the following:

1)Subtracts a cell in column B from a cell column C, puts that value in
column D
2)If value of column D is >1,000 then copy cell in Column A&D and paste on a
different sheet.

The code works for fine. My question is this – can this be done in one step?
Can the subtraction of the two cells and pasting to values on the second sheet
be accomplished all at once?

Is there a way to capture the value from the subtraction of the two cells and
based on a condition paste it on the 2nd sheet?

Sub doformulas()
Dim cell As Range
Dim lastrow As Integer

'define last row
lastrow = Sheets("Sheet3").Range("A4").End(xlDown).Row
'calculate what ea. customer still owes
With Range("d4:d" & lastrow)
.Formula = "=RC[-2]-RC[-1]"
.Formula = .Value
End With

'based on that calculation, copy customer no. _
'& balance owed to Sheet4

'copy customer no.
For Each cell In Sheets("sheet3").Range("a4:a" & lastrow)
If cell.Offset(, 3) > 1000 Then
cell.Copy
'paste on sheet4
Sheets("Sheet4").Select
Range("a4").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
Selection.PasteSpecial (xlPasteValues)
End If
Next
'copy balance owed
Sheets("sheet3").Select
For Each cell In Sheets("sheet3").Range("a4:a" & lastrow)
If cell.Offset(, 3) > 1000 Then
cell.Offset(0, 3).Copy
'paste on sheet4
Sheets("Sheet4").Select
Range("b4").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
Selection.PasteSpecial (xlPasteValues)
End If
Next

Application.CutCopyMode = False

End Sub

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on Easier method to capture & paste calculated value? Or get search suggestion and latest updates.




Tagged: