Dan
Premium Member
Registered: 22nd Apr 02
Location: Gorleston on Sea, Norfolk
User status: Offline
|
Morning,
I have an excel document made which is a timesheet for employees.
At present, you have to load each person and change the final date and print individually.
I want to be able to open 1 spread sheet and merge a list of names and print all documents in one go, making it much faster.
How do i do this?
Adult GiftsClick here to vist us
|
Dan
Premium Member
Registered: 22nd Apr 02
Location: Gorleston on Sea, Norfolk
User status: Offline
|
Anyone??
Adult GiftsClick here to vist us
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Can you not merge the lot into one workbook over multiple worksheets?
If you can do that then you can use VBA to change the date and print all of the worksheets in one lot.
If you can post an example of what you got and what needs doing then someone on here will help 
|
Dan
Premium Member
Registered: 22nd Apr 02
Location: Gorleston on Sea, Norfolk
User status: Offline
|
Put all the sheets onto one book? I guess. What I could do, is put all into 1 workbook. Then have the date field on the first page.
Have the cells all linked to a master cell and change date in that.
Hmm
Adult GiftsClick here to vist us
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
You could write some VBA which consolidates each workbook into a single workbook (or even worksheet depending on the data).
More hassle than it's worth though, you'd need to lock all the 'template' workbooks down as when someone inserts a row/column (which they will) it will likely break the run, if people are in workbooks when you do the run it won't pull the data. We have no end of problems trying to do stuff in excel that it's not designed for.
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
Maybe going a little around the houses here but could you not mail merge the info into a word document to print off?
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
^ I thought that too, depends if you want to do anything off the back of the output. That said, could always mail merge it into a Word table and paste it back into Excel. Bit of a ball ache though.
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
I'd say it was the easiest solution to put in, and for when he isn't around someone else could pick it up easily.
[Edited on 18-11-2013 by Gaz]
|
Dan
Premium Member
Registered: 22nd Apr 02
Location: Gorleston on Sea, Norfolk
User status: Offline
|
A mail merge wont work, as you cant mail merge excel to excel easily.
Its no where near as complicated as most of the above.
Its a template someone made which prints as a perfect a4 time sheet, with days, hours etc etc, which can then be filled in by pen.
All I really need to do is simplfy the changing date and the name. So I can print all at once, rather than individually.
I think putting them all into one book, then linking the date in all sheets, to a master sheet. I can then change the master date, which should alter all the dates.
Adult GiftsClick here to vist us
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
You don't even need to put them all in one workbook to do that, you can just link the cells to a 'date' cell in a separate workbook.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Alternatively you could stick the template on one sheet, list of names on another then use a macro/VBA to loop through the names and on each pass replace the name and date on the template and send it to the print queue - piece of piss to do and might be easier if you ever need to change the template or add extra names.
quote:
Originally posted by A2H GO
You don't even need to put them all in one workbook to do that, you can just link the cells to a 'date' cell in a separate workbook.
Makes it a little messy to manage plus wouldn't it still mean printing requires open each individual workbook?
[Edited on 18-11-2013 by Dom]
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
Yep not ideal, much easier just putting them all in one workbook.
|
Dan
Premium Member
Registered: 22nd Apr 02
Location: Gorleston on Sea, Norfolk
User status: Offline
|
Just to update.
Sorted this. Put all worksheets into one workbook. 1st sheet has the week end date.
Linked all dates on each sheet to the master sheet date.
Now they can change one date and print the workbook. It's easy to add in new names and delete old ones.
Cheers!
Adult GiftsClick here to vist us
|
