200+ sheets need changed

[TYOsborn]

Hello,

I have over two hundred sheets that need changed. Short of copying, pasting and inserting a row, I don't know how to achieve this. Unfortunately I have little to no experience with VB thought it is a goal of mine to master someday. I'm hoping it won't be time consuming for someone that knows what they are doing to figure out. Basically I need for the tab called "yearly stats" to have all of the 2012 stats copied to the 2012 row on the new worksheet (2013) and also copying the tabs called "Action item" and "Review and Improve". All of the files will have different names, but the tabs in each of the files will have the same names. Another catch is that the 2013 worksheet that I need the 2012 info copied to has some additional rows added and some verbiage change. The new areas would just be left blank since they will only be applying to 2013. I know this is a leap, but I'm hoping someone can either tell me how to do it or be so kind as to write a macro for me. It will be a life saver either way! I've attached an example of a sheet I need to get info from (CQI Master County Indiana) and the sheet I need to get info to (CQI Blank Master County 2013). Thanks so much in advance!

Tara

[p24leclerc]

See the attached file. It contains a macro named Year_Transfer. This macro will take the actual file and copy the data from this year rows to the previous year rows and change the descriptions in column B to reflect year changed.

If you want to add new rows or change some verbiage, we can do so in another macro which will be specific for this task.

If you have all your files in a directory, the macro can be changed to process all of them automatically.

[TYOsborn]

Thanks so much for trying, but I recorded a macro that will change the date and shift the data down, but it's the new information on the 2013 sheet that needs to be included. That's why I was requesting the data be copied from the old sheet into the new 2013 sheet.

[TYOsborn]

I do have to say your macro is much faster than my recorded macro. I actually wish there was another way to dump this information instead of using Excel. On top of everything else I have those 200+ spreadsheets linked to a master spreadsheet so that reports can be run. Thanks again for your help!

[p24leclerc]

Hi Tara,
I'm not sure I'm following your thinking here.
Do you need anything else done about this thread?

[TYOsborn]

Yes please. The CQI Master Blank County 2013 is the preferred file to use. There have been rows added to it and verbiage changes to update to 2013. Your macro was very quick, but did shift data down in the last section which put it out alignment. It also needs to clear the data out of the 2013 row so that it is prepared for input when the months come. I'm totally game for a 2nd macro that would insert the required rows and add the correct verbiage. Anything you can come up with is greatly appreciated. Thanks again!

[TYOsborn]

Just had a thought... Would it be possible to have the data spreadsheet be a form and once the form is complete, they could click on a "generate" button it would populate the corresponding blank cells on another tab? I don't know how detailed or difficult that would be, but I can say I've seen some pretty amazing stuff done in this forum.

[p24leclerc]

Hi Tara,
Project is a lot more complicated than anticipated. First, because you changed rows description in your blank sheet too early. There is no way to match information from the old files with the new one if descriptions are not the same. Even a dash (-) would make a huge difference in a text search.

Thus, I changed the descriptions of rows in the following workbook: “CQI Blank Master County2013-2.xlsx”. I also removed the 2 other sheets so there is only one sheet “Yearly Stats”. I was not able to match all rows from old file. Especially in the Mental Health section because descriptions were too different and I was not sure enough to make the changes. You’ll have to do it yourself. Best way to go is to copy the description from the old file to this one. In this file, you can add rows but not change rows’ description. New rows will not affect the macro which will leave the month’s cells blank.
I saved the new descriptions in another file named “Blank Master new verbiage.xlsm” which also contains the macro.

Here is how things should be done:
1. Make sure both files are in the same directory (Blank Master new verbiage.xlsm and CQI Blank Master County2013-2.xlsx). You should respect the names as they are specifically called in the macro. If you change the names, do not forget to change it in the macro too.
2. Open Blank Master new verbiage.xlsm file and run the macro named Year_Transfer.
3. A window will open asking you to select the file to process. Select one file.
4. After that, everything is automatic. Data will be copied to proper cells. Original file will be closed without saving (no change is done to it). Descriptions will then be updated. The 2 sheets “Action items” and “Review and Improve” will be copied to new file. Title will be copy to new file and new file will get the name “2013-old file name here” and it will be saved to the same directory you placed the files Blank Master new verbiage.xlsm and CQI Blank Master County2013-2.xlsx.
5. At the end, there is only one open workbook “Blank Master new verbiage.xlsm”. You can rerun the macro to process another file.

If you want, all files can be processed automatically provided they are all in the same directory. I can make the changes to the macro if you’d like.

If you ask other people to do this, you have to provide them with those 2 files along with instructions on how to proceed. They will have to copy the new file created by the macro to where you want the files to be.
Hope you're not afraid as it is simpler then it seems.
Regards

[TYOsborn]

Holy cow! This is awesome. Will you marry me? Kidding of course... Thank you so much for you time and effort. It's greatly appreciated. You've saved me hours I'm sure. Thanks again! I hope to be able to this type of work myself someday soon.