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

[Shaun4d]

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

[Paul_Hossler]

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 = ...

[SamT]

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.

[Artik]

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,C11 2," & _
"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

[Shaun4d]

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,C11 2," & _
"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.

[SamT]

@ 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.

[snb]

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.

[Artik]

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. :bow:

Artik