Consulting

Results 1 to 7 of 7

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

  1. #1
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location

    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

    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

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,743
    Location
    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
    Last edited by Paul_Hossler; 11-09-2023 at 07:38 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    I have attached file
    Attached Files Attached Files

  5. #5
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    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.
    Attached Files Attached Files

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,743
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Regular
    Joined
    Sep 2023
    Posts
    75
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •