copying lots of cells

[chrischarles]

Hi everybody,

I know Excel reasonably from the last few months but I had not done much VBA until recently so please bear with me.

I have created a form using Excel that is a single A4 printable sheet, called Materials_Form. The data behind this form is stored in a single row in another sheet called Materials. I have created a Macro that when you select a row number from a cell at the top of the sheet then the rest of the information from the materials spreadsheet will be copied across. Now any changes can be made to the information by changing the cells and clicking save; the save process then writes this information back into the Materials spreadsheet for storing.

My problem comes from the fact that the form has input boxes in random places on the sheet (for easy display of information) and as such you need to run a copy process for every cell to be copied (26 of them). This makes the macro fairly slow (6 seconds or so) as opposed to a single copy with some VBA array processing which I do not understand. I have used some named cells on the form so you can say

[Date_Now] = Worksheets("Materials").Cells(dbRow, 2)

and vice versa when saving form data,

Worksheets("Materials").Cells(dbRow, 2) = [Date_Now]

I have turned off ScreenUpdating but it is still slow, possibly because I have a quick check on whether the row number cell has changed at the top of the sheet. i.e. in order to update the form I update the Row number cell at the top and this triggers a cellchange which fills in the rest.

I hope that makes sense and I would appreciate your help in speeding the code up please!

Thanks,

Chris

[royUK]

ScreenUpdating will only speed up marginally. The major problem is usually selecting or activating sheets and ranges.

It's hard to comment on improving code without seeing it, an example workbook would be useful

[chrischarles]

Thanks very much for your response. Sorry I have attached one now but I had to strip out some information and put in some sample stuff.

The worksheet will be shared when finished.

[royUK]

Where's the attachment?

[chrischarles]

It isnt letting me attach excel files for some reason, keeps giving me database errors. I am now trying a different browser and have zipped it up.

[chrischarles]

Seems to have attached above now. The code which I think is slowest are the Fill_In_Database and Fill_In_Form functions as these are the sections that require loads of copy operations...

Thank you very much again.

[royUK]

I can't see why that code should be slow.

[chrischarles]

Damn, I was hoping there was a clever way of doing the copy process so that it is a single command and can be done much quicker in operation. I have tried a few ideas with copying a whole range and processing it using range.areas and range.areas.count but I cannot get them working.

Would that not speed it up massively as I was hoping?

Thanks again

[snb]

This code will be sufficient (and much faster)
There's no need to use namedranges.
In this example I filled in upto 'system2' (cell H7), that you will find in the code as cells(7,8).

Please Login or Register  to view this content.

[chrischarles]

Im not sure I really understand that code Im afraid. What does the resizing do? I have 22 items in my database row rather than 17 so should I change that? Also, I tried it with just the first 4 in place as you mentioned and it didnt do anything to the database so I can work out what it is setting when...

Thanks again