Using VBA to copy and paste the content of multiple worksheets to a new files

[rugbyfitz]

Firstly thanks for reading my thread.

I am new to VBA and am trying to develop some code to copy data from a number of different files to one workbook.

To explain, I am sent about 15 different files on a daily basis and need to copy the data from one of the worksheets in each of the files (on the second worksheet named ?compiled?) to one template named ?daily compiled?.

The data copied from this worksheet will need to pasted to a specific worksheet in the new template file. For example Sarah will need to be copied to the ?sarah? worksheet and Tom will need to copied to ?Tom? work sheet.

The range of the cells to be copied is D16:F57 and as I said previously this will need to be copied from file Sarah, Tome ect and been located on sheet name ?compiled? in D16:F57

The files to be copied will be stored on my desktop in a folder name tracker details

I hope that explains it well enough, I will be monitoring the post so if you have any questions please let me know.

Thanks in advance

[RonMcK]

rugbyfitz,

Tell me more about the target file: 'compiled'. Does it have one worksheet for each source file? Is there one 'complied' file per business day?

Thanks,

[rugbyfitz]

rugbyfitz,

Tell me more about the target file: 'compiled'. Does it have one worksheet for each source file? Is there one 'complied' file per business day?

Thanks,

Hi there

cheers for your prompt responce.
Ok so i recieved a number of daily files each of these has a number of worksheets. However its the 'compiled' worksheet that i want to be copied to a new file.

Within the new file there will be seperate worksheets with a template ready to contain the copied data from all the daily files.


at the moment i have to go through open each of the files sent to me, located the compiled worksheet, copy the selected range and past this into a new template in the approiate worksheet. Its this manual process that im hoping to remove.

[RonMcK]

rugbyfitz,

Summarizing: you receive 15 workbooks each day, copy the selected range from the 'compiled' worksheet of each workbook and paste it to a separate worksheet in the new 'compiled' workbook. Tomorrow, youi'll receive another 15 workbooks and repeat the process.

What are the names of the source workbooks? Is there a naming convention that you use for source and target workbooks? Do the target worksheet names already exist in the template workbook or should the process create them? Do you move the source workbooks into a specific folder for processing? Is the master copy of the target workbook kept in a particular folder? Do you want the new 'compiled' workbook saved to a particular folder?

Food for thought (and future modifications):
What do you want to do about a missing source workbook? How often do the people reporting to you change? Might the number reporting increase?

[rugbyfitz]

Hi Ron

in terms of the process, yes that?s correct, its basically a way of reporting on what?s been achieved each day by the individuals submitting the reports.

The names of the new workbooks at present are the individuals name eg Carlie followed by the date the report refers to. However this could potentially change if required as a degree of manual work is needed to save the files initially from my emails.

The target worksheet names do already exist within the template and the cells copied need pasting to the following destination: D16:F57 within each of their relative names worksheets

The source folders are moved into a specific folder for storing however i cant tell you the address as every time i try to submit the message it tells me i dont have enough posts although this the location is not particularly important.

In terms of saving the new compiled workbook to a folder that sounds like a good idea but am not overly fussed on the location

Food for thought

Missing files: there will be missing files, due to staff absences, we have a very simple excel function running that indicates which are missing. This is satisfactory

Yes there is potential for all the number and names of people to change although I feel I will have to deal with as a separate project

[rugbyfitz]

would you like me to send you the files so you have a look yourself?

[RonMcK]

rugbyfitz,

It would help as long as it doesn't compromise proprietary or confidential data. If those are problems, then, provide me with the names you use for your target file and the tabs in it (as compared to the source file names, which I understand are in the form: 'myname' + 'today's date'.xls).

Thanks,

[rugbyfitz]

would it be possible to have a personal email address as i dont want to publish the files online?

[RonMcK]

Did you find the PM I sent you?

[rugbyfitz]

sure did, just putting together the email now.
cheer for your help with this
regards
Nick

[Aussiebear]

Ron & rugbyfitz, any chance of the solution being put online for all to see? I can understand the need to confidentiality in relation to work material, but others might well be interested in what solution you arrived at.

[kiyiya]

I second that.

[mdmackillop]

A utility for saving

[RonMcK]

Ron & rugbyfitz, any chance of the solution being put online for all to see? I can understand the need to confidentiality in relation to work material, but others might well be interested in what solution you arrived at.

Aussiebear, et al,

You haven't been missing anything. I'm finishing up the code this evening after getting some help from Malcolm, yesterday. I will post the solution here when it's done.

Cheers!

Ron

[RonMcK]

Nick, Aussiebear, Kiyiya, et al,

Attached is a copy of the completed program. Let me recap what the requirements were.

Nick needed a program that would read the individual workers' daily time tracker worksheets and copy a range of data from each worksheet into that worker's worksheet in the supervisor's summary workbook containing one sheet for every worker. These sheets are linked to 3 summary pages used for analysis and reporting. In Nick's instance there are 2 supervisors.

The daily summary file is built from a template (.xlt) file (one for each supervisor). These daily files' names include the date of the work. When the program encounters an existing summary file during the build process, the program prompts for overwrite permission. The program does not flag any missing worker files because one of the worksheets in the file already reports that information.

Since Nick wants to learn VBA programming, I heavily commented the program for his edification.

Please let me know if you have any questions.

Cheers!