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

[Scuba]

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


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

[June7]

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.

[Paul_Hossler]

Three changes




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

[Scuba]

I have attached file

[Scuba]

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.

[Paul_Hossler]

You never called the sub.

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



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

[Scuba]

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.