Auto populate

[Dave H9]

I am trying to create a document register in a workbook which auto-populates from a seperate workbook containing several different documents within different tabs. Multiple users will complete the documents and all contain similar information (although in different locations), ideally I would like to add a button on each document which once pressed copies the relevent information into the first available blank row in the register.

Hope I have explained the problem clearly, thanks for your help.

[TMS]

It shouldn't be too difficult to do.

However. there is a certain element of vagueness about your question.

It would help if you provided a sample register and a sample documen with some typical data in it. You need to indicate what data is copied, from where (tab/cells) and to where (gain, tab/cells)

Regards

[Dave H9]

TMShucks

Thank you for the reply. I have attached (hopefully) two workbooks.

"Example register" is the one I need to auto-populate.
"Example documents" is the source file and has two tabs with simplified examples of data which will be added by multiple users.

We would probably produce 20+ documents a day which would gradually produce a monthly register of the details so the macro would need to populate the first available blank row each time a new document is created.

Thank you very much for your help

Regards

Dave

Example register.xls

Example documents.xls

[Dave H9]

Has anyone any suggestions please?

[royUK]

It would be easier to have a hidden sheet in the document that summarised the inputs to one row, then simply copy that row.

I would remove the Merged Cells though

[HardyRexion]

I have code I have written that allows users to click a button that opens an existing work book, looks for the next blank cell and writes the information.

I'll make it more readable and post it here ASAP

[Dave H9]

royUK - excellent suggestion thank you.

HardyRexion - Thank you, that button is the key element of what I am trying to acheive.

Thank you both for your contribution.

[HardyRexion]

Ok, here is a stripped down version that shows where you need to enter your specific information.

Please Login or Register  to view this content.

This code works fine for me.

Obviously just put the routine's name in the click event of the button

Please Login or Register  to view this content.

[royUK]

I haven't had time to check this. Summarise the inputs to Sheet2 of the document then close & save
This code prompts to select the workbook then copies the range A1 to A10 to the main workbook. Adjust the range reference to suit

Please Login or Register  to view this content.

[Dave H9]

Thank you both. I am a bit of a novice on code so I'll take a little while to apply this to my problem. I'll get back once I've done that.

Thanks again

Regards

[Dave H9]

HandyRexion,

How do I specify the range to copy?

royUK,

I can't get this to work as my source has multiple tabs

Please Login or Register  to view this content.

is it possible to allow the tab selection when the "file to zip" is selected and I can hide the source data within each tab?

Thanks again.

[Dave H9]

OK nearly got HardyRexion's code working for me. I have added the following -

Please Login or Register  to view this content.

and altered

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

When I hit the button to launch the code I can see the correct info is copied to the clipboard, also the correct workbook opens and correctly selects the first blank cell in column A, however the error Number 438 is generated. I'm guessing the paste I added is causing this. How do I get the paste function to work?

As I said I am a novice so your patience and help is greatly appreciated.

Regards

Dave

[snb]

If you appreciate the contribution of several users to the same register, you'd better use:

Please Login or Register  to view this content.

[royUK]

Please Login or Register  to view this content.

strToWrite is a String so you can't use Copy with it like that.

For my code

Which sheet are you summarising the data to ?

[Dave H9]

snb, what does your code do? is it a log file?

Here is the code as modified, I have got it to work as required but could do with knowing how to specify a specific worksheet on the destination workbook -

Please Login or Register  to view this content.

The copy function seems to work but not sure if it breaches standard practice! Also I had to use the paste special function to avoid just pasting cell references.

royUK,
With your code I am summarising on several different sheets within a file called permits then trying to copy that to the destination (register).

Thank you all again for you contributions, this has been a great introduction to VBA for me.

Regards

[royUK]

That code to find the next empty row is really inefficient. It only opens the specific document, I thought you had several possible documents to import from

If it's one workbook with multiple users consider using a template workbook (*.xlt) that a user opens, completes then saves it. Having multiple sheets for completion in one workbook just makes maintenance of the task complicated

[snb]

snb, what does your code do? is it a log file?

Yes, that's a logfile (the simplest, fastest and most robust).
Just try it.
If you want to use a csv-file:

Please Login or Register  to view this content.

[Dave H9]

royUK,

The source documents are all contained within one workbook on several worksheets. The code in my last post seems to be working well. Given this would you recomend any alterations to improve efficiency.

snb,

Thank you I will give it a go.

Again a big thank you to all for taking the time to help and humouring a novice.

[Dave H9]

snb,

I can't seem to get your code to work. I have put it in a new module within the document register(.xls). Also I have changed the file path to reflect my system and created the file "register.txt". Closing my workbookdoes not populate the text file. Had a read in the help file and tried using your code in a class module instead, stillnot working . Any suggestions?

Thank you

[snb]

Cfr. the attachment.

Open it and close it: macros enabled.
Look for C:\register.csv

[Dave H9]

Thanks snb, I will use that.