Consulting

Results 1 to 10 of 10

Thread: Copy a range of cells

  1. #1
    VBAX Regular
    Joined
    Nov 2014
    Location
    Idaho
    Posts
    36
    Location

    Copy a range of cells

    Hi All,

    I have a UserForm that is ran on a sheet called "fieldDataCalc" where a user will enter data into specific fields and upon pressing the "Accept Data" CommandButton, the data will be dumped into "fieldDataCalc". After that, I want the data to be copied and pasted on the next available line on another sheet called "fieldData".

    So far, the only VBA Code I have is for the "Accept Data" action...
    Private Sub cmdAccept_Click()
        Dim RowCount As Long
        Dim ctl As Control
             
        RowCount = Worksheets("fieldDataCalc").Range("A2").CurrentRegion.Rows.Count
        With Worksheets("fieldDataCalc").Range("A2")
            .Offset(RowCount, 0).Value = Me.txtBox_fieldDataCalc_dateDelivered.Value
            .Offset(RowCount, 1).Value = Me.comboBox_fieldDataCalc_fieldName.Value
            .Offset(RowCount, 2).Value = Me.txtBox_fieldDataCalc_acres.Value
            .Offset(RowCount, 3).Value = Me.comboBox_fieldDataCalc_crop.Value
        End With
        
        RowCount = Worksheets("fieldDataCalc").Range("E2").CurrentRegion.Rows.Count
        With Worksheets("fieldDataCalc").Range("E2")
            .Offset(RowCount, 0).Value = Me.txtBox_fieldDataCalc_product1_gallonsLoaded.Value
            .Offset(RowCount, 1).Value = Me.txtBox_fieldDataCalc_product2_gallonsLoaded.Value
            .Offset(RowCount, 2).Value = Me.txtBox_fieldDataCalc_product3_gallonsLoaded.Value
            .Offset(RowCount, 3).Value = Me.txtBox_fieldDataCalc_product4_gallonsLoaded.Value
            .Offset(RowCount, 4).Value = Me.txtBox_fieldDataCalc_product5_gallonsLoaded.Value
            .Offset(RowCount, 5).Value = Me.txtBox_fieldDataCalc_product6_gallonsLoaded.Value
        End With
    
    
    End Sub
    ...and...

    Option Explicit
    Private Sub CommandButton1_Click()
        UserForm1.Show
    End Sub
    , which is on the fieldDataCalc sheet.

    dataEntryPage.jpg
    fieldDataCalc.jpg

    I appreciate any assistance offered!

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello netwerkz,

    What is your code not doing that you it want it to do?
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Basically 1 line suffices:

     sheets("fieldDataCalc").cells(rows.count,1).end(xlup).resize(,10)=array(txtBox_fieldDataCalc_dateDelivered,.comboBox_fieldDataCalc_fieldName,txtBox_fieldDataCalc_acres,comboBox_fieldDataCalc_crop,txtBox_fieldDataCalc_product1_gallonsLoaded, 
     txtBox_fieldDataCalc_product2_gallonsLoaded,  ,,,,).Value
    NB. If you reduce the length of the textboxnames & comboboxnames it's all much easier to write.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    After that, I want the data to be copied and pasted on the next available line on another sheet called "fieldData".




    Should this be called "fieldData" and not "fieldDataCalc"?


    RowCount = Worksheets("fieldDataCalc").Range("E2").CurrentRegion.Rows.Count 
        With Worksheets("fieldDataCalc").Range("E2")
    ---------------------------------------------------------------------------------------------------------------------

    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

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    snb, does an array have a .Value property?
    Last edited by p45cal; 07-17-2016 at 09:01 AM.

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    You can amend it if you like ...

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    the OP will have to…

  8. #8
    VBAX Regular
    Joined
    Nov 2014
    Location
    Idaho
    Posts
    36
    Location
    Quote Originally Posted by Leith Ross View Post
    Hello netwerkz,

    What is your code not doing that you it want it to do?
    When the user opens the userform, they will enter the data and click the Accept button. Upon click, the data does drop into the fieldDataCalc sheet and the formulas are ran correctly. What I want to happen afterwards is to have the calculated data be copied over to another worksheet called fieldData. After the row is populated, the user will enter new data and that will follow the same process, which will be populated on the next available line on fieldData.

  9. #9
    VBAX Regular
    Joined
    Nov 2014
    Location
    Idaho
    Posts
    36
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Should this be called "fieldData" and not "fieldDataCalc"?


    RowCount = Worksheets("fieldDataCalc").Range("E2").CurrentRegion.Rows.Count 
        With Worksheets("fieldDataCalc").Range("E2")
    Hi Paul, I'm not sure if the question was directed at me, but I'll comment to help clarify. The two sheets are similarly named, but both have different uses. The fieldData sheet is where all of the calculated output data is compiled and is used for reporting to the client. The fieldDataCalc page is used to take input data, make a combination of specific calculations.

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    @netwerkz

    I have a UserForm that is ran on a sheet called "fieldDataCalc" where a user will enter data into specific fields and upon pressing the "Accept Data" CommandButton, the data will be dumped into "fieldDataCalc". After that, I want the data to be copied and pasted on the next available line on another sheet called "fieldData".

    Hi Paul, I'm not sure if the question was directed at me, but I'll comment to help clarify. The two sheets are similarly named, but both have different uses. The fieldData sheet is where all of the calculated output data is compiled and is used for reporting to the client. The fieldDataCalc page is used to take input data, make a combination of specific calculations.

    I was asking because the code below does NOT appear to do what's in red. I wasn't sure if the below should have been just 'fieldData', or if the part in red above comes later


        RowCount = Worksheets("fieldDataCalc").Range("E2").CurrentRegion.Rows.Count 
        With Worksheets("fieldDataCalc").Range("E2") 
            .Offset(RowCount, 0).Value = Me.txtBox_fieldDataCalc_product1_gallonsLoaded.Value 
            .Offset(RowCount, 1).Value = Me.txtBox_fieldDataCalc_product2_gallonsLoaded.Value 
            .Offset(RowCount, 2).Value = Me.txtBox_fieldDataCalc_product3_gallonsLoaded.Value 
            .Offset(RowCount, 3).Value = Me.txtBox_fieldDataCalc_product4_gallonsLoaded.Value 
            .Offset(RowCount, 4).Value = Me.txtBox_fieldDataCalc_product5_gallonsLoaded.Value 
            .Offset(RowCount, 5).Value = Me.txtBox_fieldDataCalc_product6_gallonsLoaded.Value 
        End With
    ---------------------------------------------------------------------------------------------------------------------

    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

Posting Permissions

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