PDA

View Full Version : Extract from worksheet into new workbook, for daily reporting.



pljharvey
04-13-2010, 04:45 AM
Hello!

Right, this is my first post... I'm pretty much a complete novice when it comes to Macros, as I've only ever recorded them to do simple repetitive tasks

But, I'm now trying to expand the use of Excel within my daily role to make myself more efficient.

Each day, I generate a number of excel files used to calculate payment notices. So, they currently calculate any pay-offs and pump the data into sheet 1 of my workbook. I then save this workbook per it's name and email a copy of the sheet 1 to the other teams that require it

What I'm hoping to do, is just create a macro button somewhere on sheet 1 of this template, that then creates a separate workbook for "today's date" and copies the relevent data into row 2 of this created workbook... This process would then be repeated throughout the day, adding data to the next available empty row.

Than, at the end of the day I can just send out one file, with all of today's work on it, instead of a separate email/file as and when.


Is this possible to do? And is it very difficult?

Rgds,
Paul

GTO
04-13-2010, 05:32 AM
Greetings Paul,

Welcome to VBAX :hi:

Your request sounds very doable and not too awfully difficult. You sound as if you want to learn a bit more in vba, and you have come to a great place to do so.

I would suggest zipping two small workbooks with some sample data, one wb to represent the 'template' or permanent wb, and the other to show what we are looking to create.

Include in the parent wb any code you have recorded, and we can go from there.

Mark

pljharvey
04-13-2010, 06:46 AM
HI Mike

Thanks for your response.

I've attached a couple of files here... one is the template, with one sheet "output" - this feeds from other sheets in the book, but I don't think they're necessary here, as I'm guessing that the Macro will read from output screen to pick bits of the relevent data that is needed.

i've also attached "Daily Report Template", which is the sort of thing I'd try to get the data pumped out into.... Hopefully then it'd save with today's date. Then, as I progress through the day, and create more "payment templates", it'd then extract that data, and add it to the next row within Today's report. (?)

Sorry if this sounds confusing...!?

Paul

pljharvey
04-13-2010, 06:47 AM
(here's the payment template - couldn't upload more than 1 file on my last post)