Using VBA to transfer data

[rohlo1be]

Hello all,

New to the forum so if there is a thread I can read that is already established, happy to view it. However, I am attempting to use VBA to send code from a submission spreadsheet to another log of the data.

Currently the code works, however, it is copying the formulas and not just the values as I have calculated fields in my submission spread sheet.

Here is the current code:

Private Sub CommandButton1_Click()
'assigns each sheet to a variable
Dim rvucalc As Worksheet
Dim prodsum As Worksheet

Set rvucalc = Sheet7
Set prodsum = Sheet1

'selects and copies all the variables in RVU calculator
Dim year As Range

Dim month As Range
Dim facility As Range
Dim discipline As Range
Dim therapist As Range
Dim amount As Range
Dim timetotal As Range
Dim untimetotal As Range
Dim rvutotal As Range

Set year = rvucalc.Range("C4")
Set month = rvucalc.Range("E4")
Set facility = rvucalc.Range("C5")
Set discipline = rvucalc.Range("G5")
Set therapist = rvucalc.Range("E5")
Set amount = rvucalc.Range("C24")
Set timetotal = rvucalc.Range("D24")
Set untimetotal = rvucalc.Range("E24")
Set rvutotal = rvucalc.Range("F24")
'Set the destination cell
Dim DestCell As Range

If prodsum.Range("A2") = "" Then 'If A2 is empty
Set DestCell = prodsum.Range("A2") '...then destination cell is A2
Else
Set DestCell = prodsum.Range("A1").End(xlDown).Offset(1, 0) '...otherwise the next empty row
End If

year.Copy DestCell
month.Copy DestCell.Offset(0, 1)
facility.Copy DestCell.Offset(0, 2)
discipline.Copy DestCell.Offset(0, 3)
therapist.Copy DestCell.Offset(0, 4)
amount.Copy DestCell.Offset(0, 5)
timetotal.Copy DestCell.Offset(0, 6)
untimetotal.Copy DestCell.Offset(0, 7)
rvutotal.Copy DestCell.Offset(0, 8)

MsgBox "Are you sure the data is correct?", vbYesNoCancel, ["Data Confirmation"]

rvucalc.Range("B7:C23").ClearContents
rvucalc.Range("E5").ClearContents


MsgBox "Form is ready for another submission", vbOKOnly, ["Submission Complete"]

End Sub


I would appreciated any help.

 

[Alex Blakenburg]

If you just want values then change the lines from this format:

Month.Copy DestCell.Offset(0, 1)

To this format

    Month.Copy
    DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
    ' OR
    DestCell.Offset(0, 1).Value = Month.Value

    ' If you want to keep the number formatting use this instead
    Month.Copy
    DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    ' If you want to keep all the formatting
    Month.Copy
    DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
    DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteFormats

 

[rohlo1be]

If you just want values then change the lines from this format:

Month.Copy DestCell.Offset(0, 1)

To this format

    Month.Copy
    DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
    ' OR
    DestCell.Offset(0, 1).Value = Month.Value

    ' If you want to keep the number formatting use this instead
    Month.Copy
    DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    ' If you want to keep all the formatting
    Month.Copy
    DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
    DestCell.Offset(0, 1).PasteSpecial Paste:=xlPasteFormats

Thank you, That worked!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.