Scuba
11-09-2023, 09:06 AM
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
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