Consulting

Results 1 to 8 of 8

Thread: Efficient Way of Copying Data from an Input Sheet to a Database

  1. #1
    VBAX Newbie
    Joined
    Jun 2021
    Posts
    2
    Location

    Efficient Way of Copying Data from an Input Sheet to a Database

    Hi,

    We currently complete a paper copy of a data entry form. I have created an electronic version (in excel) which is like for like in appearance.

    We want to start logging the data entered into the form so we can analyze it.

    I have written some code which does what I want it to do. However, I have a very limited knowledge of VBA but I know that I have not done so in the most effective way possible. I also know that if I make any changes to the layout of the data entry form, it is going to take a while to update the code.

    As you will see from the code, the data in the data entry form has no specific structure and is quite random (I don't want to change the layout of the data entry form to ensure that people remain familiar with its appearance).

    The main issues I encountered were copying data from cells that were on different rows and columns etc. and pasting onto a single row on a separate sheet.

    Please can you advise on how to 'tidy-up' the code so that it will run and can be modified more effectively.

    The code is attached.

    Many Thanks,

    Shaun
    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    It'd be a lot more useful to attach the XLSM file with the Userform(s), database worksheets, etc.

    But first thoughts ...

      Dim lastRow as Range  
    
      Set lastRow = pasteSheet.Cells(Rows.Count, "A").End(xlUp).EntireRow
    
      lastRow.cells(4).Value = copySheet.Range("c14").Value
      lastRow.cells(5).Value = ...
      lastRow.cells(6).Value = ...
    Last edited by Paul_Hossler; 06-16-2021 at 08:42 AM.
    ---------------------------------------------------------------------------------------------------------------------

    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

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    What Paul said... Show us the xlsm/xlsb file

    I would be interested to know what you call a "Data Entry Form" I have seen at least 4 different things called Data Entry Forms.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    In order not to analyze in the code whether a given range is "vertical" or "horizontal" I decided to list all cells.
    Sub Btn1_Submit()
    
        Dim varrAddr    As Variant
        Dim vResult()   As Variant
        Dim i           As Long
        Dim copySheet   As Worksheet
        Dim pasteSheet  As Worksheet
        Dim lRow        As Long
    
    
        Application.ScreenUpdating = False
    
    
        Set copySheet = Worksheets("Batch Record Sheet")
        Set pasteSheet = Worksheets("Batch Record Database")
    
    
        varrAddr = Split("B3,D3,F3,H3,C14,C15,H14,C19,C20,H19,C24,C25,H24,B32,D32,F32,B33,D33,F33,E35," & _
                         "A54,C54,F54,H54,C55,H55,C57,H57,B62,B63,B64,C64,C63,C64,D62,D63,D64,E62,E63," & _
                         "E64,F62,F63,F64,G62,G63,G64,H62,H63,H64,I62,I63,I64,B71,D71,F71,B72,D72,F72," & _
                         "A77,C77,F77,H77,C78,H78,C80,H80,B85,B86,B87,C85,C86,C87,D85,D86,D87,E85,E86," & _
                         "E87,F85,F86,F87,G85,G86,G87,H85,H86,H87,I85,I86,I87,D105,D106,A112,B112,C112," & _
                         "E112,G112,H112,A113,B113,C113,E113,G113,H113,A114,B114,C114,E114,G114,H114," & _
                         "A115,B115,C115,E115,G115,H115,A116,B116,C116,E116,G116,H116,A117,B117,C117," & _
                         "E117,G117,H117,B124,D124,F124,B125,D125,F125,C127,C129,C133,C134,C135,C133," & _
                         "C134,C135,D133,D134,D135,E133,E134,E135,F133,F134,F135,G133,G134,G135,H133," & _
                         "H134,H135,I133,I134,I135,B154,D154,F154,B155,D155,F155,C158,B162,F162,B163," & _
                         "F163,E165,A170,D170,F170", ",")
        
        ReDim vResult(0 To UBound(varrAddr))
    
    
        With copySheet
            For i = 0 To UBound(vResult)
                vResult(i) = .Range(varrAddr(i)).Value
            Next i
        End With
    
    
        With pasteSheet
            lRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            .Cells(lRow, "A").Resize(, UBound(vResult) + 1).Value = vResult
        End With
    End Sub
    Artik

  5. #5
    VBAX Newbie
    Joined
    Jun 2021
    Posts
    2
    Location
    Quote Originally Posted by Artik View Post
    In order not to analyze in the code whether a given range is "vertical" or "horizontal" I decided to list all cells.
    Sub Btn1_Submit()
    
        Dim varrAddr    As Variant
        Dim vResult()   As Variant
        Dim i           As Long
        Dim copySheet   As Worksheet
        Dim pasteSheet  As Worksheet
        Dim lRow        As Long
    
    
        Application.ScreenUpdating = False
    
    
        Set copySheet = Worksheets("Batch Record Sheet")
        Set pasteSheet = Worksheets("Batch Record Database")
    
    
        varrAddr = Split("B3,D3,F3,H3,C14,C15,H14,C19,C20,H19,C24,C25,H24,B32,D32,F32,B33,D33,F33,E35," & _
                         "A54,C54,F54,H54,C55,H55,C57,H57,B62,B63,B64,C64,C63,C64,D62,D63,D64,E62,E63," & _
                         "E64,F62,F63,F64,G62,G63,G64,H62,H63,H64,I62,I63,I64,B71,D71,F71,B72,D72,F72," & _
                         "A77,C77,F77,H77,C78,H78,C80,H80,B85,B86,B87,C85,C86,C87,D85,D86,D87,E85,E86," & _
                         "E87,F85,F86,F87,G85,G86,G87,H85,H86,H87,I85,I86,I87,D105,D106,A112,B112,C112," & _
                         "E112,G112,H112,A113,B113,C113,E113,G113,H113,A114,B114,C114,E114,G114,H114," & _
                         "A115,B115,C115,E115,G115,H115,A116,B116,C116,E116,G116,H116,A117,B117,C117," & _
                         "E117,G117,H117,B124,D124,F124,B125,D125,F125,C127,C129,C133,C134,C135,C133," & _
                         "C134,C135,D133,D134,D135,E133,E134,E135,F133,F134,F135,G133,G134,G135,H133," & _
                         "H134,H135,I133,I134,I135,B154,D154,F154,B155,D155,F155,C158,B162,F162,B163," & _
                         "F163,E165,A170,D170,F170", ",")
        
        ReDim vResult(0 To UBound(varrAddr))
    
    
        With copySheet
            For i = 0 To UBound(vResult)
                vResult(i) = .Range(varrAddr(i)).Value
            Next i
        End With
    
    
        With pasteSheet
            lRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            .Cells(lRow, "A").Resize(, UBound(vResult) + 1).Value = vResult
        End With
    End Sub
    Artik
    This worked perfectly! It runs so much faster and will make my life so much easier when the "Batch Record Sheet" changes. Thank you very much.

    Thank you very much to all the others who have helped too.

    I just need to study the above code so I can understand what it is actually doing.

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    @ Artik: Good guess.

    @ Shaun: Glad you got what you needed
    I see you were using a Data Entry Sheet, not a Data Entry Form

    The main difference is that with a VBA Data Entry UserForm is that navigating from one Field to the next is a single Key Press.
    Last edited by SamT; 06-17-2021 at 01:36 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    If you add a reference to all the cells you need, starting in e.g. K1

    in K1: =B3
    in L1: =D3
    in M1: =F3
    in N1: =H3
    in O1: =C14
    in P1: =C15 , etc

    The only code you need is:
    Sub M_snb() 
       sheets("Batch Record Database").cells(rows.count,1).end(xlup).offset(1),resize(,205)=sheets("Batch Record Sheet").cells(1,11).resize(,205).value
    End Sub
    Remember: structuring precedes coding.
    Last edited by snb; 06-17-2021 at 01:46 AM.

  8. #8
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Very nice snb idea to solve the problem. In addition to the much shorter code, preparing appeals to the competent cells directly on the sheet is more user-friendly than creating them in the code.

    Artik

Posting Permissions

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