VBA Code to copy 4 cells from one worksheet to another Worksheet

Hello,

I am trying to code some data to copy 4 specific cells from one worksheet to another worksheet, however, nothing happens when I run this data, I have tried the below code

Code:

---

Public Sub TotalsUpdated()
' Define the source and destination worksheets
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
' Set the source and destination worksheets
Set sourceSheet = ActiveWorkbook.Worksheets("FormsControl Sheet")
Set destSheet = ActiveWorkbook.Worksheets("Rate Calc")
' Define the fields to copy
Dim sourceFields() As String
sourceFields = Array("H37", "J37", "F37", "D37")
' Define the destination cells
Dim destCells() As String
destCells = Array("C9", "C10", "C11", "C12")
' Copy the fields from the source worksheet to the destination worksheet
Dim i As Integer
For i = 0 To UBound(sourceFields)
    destSheet.Cells(destCells(i)).Value = sourceSheet.Cells(sourceFields(i)).Value
Next i
End Sub

---

This code is stored in the Forms and when run called a forms menu is called up that the user inputs data in and said data is then stored in the worksheet called 'FormsControl Sheet', i need to then copy the data from the FormControl sheet to the Rate Calc sheet.

Can anyone advise where am I going wrong?

thanks

Richard

This code is in Excel? Could provide file for analysis.

I tested your code and immediately get "type mismatch" on sourcefields. Declare arrays as Variant.

Now I get "Invalid procedure or argument" in the loop. Use Range instead of Cells.


Advise to place all Dim statements at beginning of procedure.

Three changes

Code:

---

Option Explicit

Public Sub TotalsUpdated()
' Define the source and destination worksheets
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
' Set the source and destination worksheets

Set sourceSheet = ActiveWorkbook.Worksheets("FormsControl Sheet")
Set destSheet = ActiveWorkbook.Worksheets("Rate Calc")

' Define the fields to copy
Dim sourceFields As Variant    '  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
sourceFields = Array("H37", "J37", "F37", "D37")

' Define the destination cells
Dim destCells As Variant    '  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
destCells = Array("C9", "C10", "C11", "C12")

' Copy the fields from the source worksheet to the destination worksheet
Dim i As Integer
For i = 0 To UBound(sourceFields)
    destSheet.Range(destCells(i)).Value = sourceSheet.Range(sourceFields(i)).Value  '  <<<<<<<<<<<<<<<<<<<<<<<<<
Next i

End Sub

---

I have attached file

Hi Paul,

I adjusted to match your code but still didn't work I'm afraid.
I've attached file for reference if this helps.

You never called the sub.

You'll have to add the call where you need it

Code:

---

Public Sub UpdateCBMTotalAir()
    'Get the values of the 6 textboxes
    Dim TextBox43Value As Double
    Dim TextBox44Value As Double
    Dim TextBox45Value As Double
    Dim TextBox46Value As Double
    Dim TextBox47Value As Double
    Dim TextBox48Value As Double


    TextBox43Value = Val(CBM1Air.Text)
    TextBox44Value = Val(CBM2Air.Text)
    TextBox45Value = Val(CBM3Air.Text)
    TextBox46Value = Val(CBM4Air.Text)
    TextBox47Value = Val(CBM5Air.Text)
    TextBox48Value = Val(CBM6Air.Text)


    'Calculate the total
    Dim Total As Double
    Total = TextBox43Value + TextBox44Value + TextBox45Value + TextBox46Value + TextBox47Value + TextBox48Value


    'Display the total in the total textbox
    TotalCBMAir.Text = Format(Total, "0.00")
   
    TotalsUpdated


End Sub

---

Sometimes you can't see the wood for the tree's as the saying goes.

School boy error!

Thank you Paul for pointing me in the right direction.