Efficient Way of Copying Data from an Input Sheet to a Database (1 Viewer)

[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 analyse 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

 

[The_Doc_Man]

You omitted something that we need to know. Is the end goal to make this in a differently formatted spreadsheet? (Which is what your code appears to do.) Or is it to bring into an Access database? Makes a difference, and I can't tell from reading.

Based on the code, your VBA is running in Excel context completely since you didn't open an Applications Object. You just started working on spreadsheets directly. But it is possible that you could have done something outside of that button that launches the code. Unfortunately, we can't tell the context of that button.

Stated another way ... you showed us the trees but we also need to see a little bit of the forest.

 

[Shaun4d]

Yes, the end goal is copy the data from the input form to a table on a separate sheet within the same workbook.

No, its just a button at the bottom of the data entry form that launches the code.

 

[arnelgp]

you can make a Table and Form in ms access.
it will save you the trouble of having lots of code.
you only need to Export the table of msAccess to Excel.