Open Many Excel work books, (And more.....)

Hi Everyone.

I'm looking for some help if possible please.

I currently work for a university and the tutors are using excel spreadsheets to input attendance. Each week I need to create a copy of that weeks attendance and then hide the week I just copied so it can't be seen by students (to create a copy of the attendance - at the moment - I print each week/page as a PDF)

At the moment I only have about 20 Workbooks to deal with so it's not a major job however in a few months time the amount of workbooks will increase to around 200 across 4 courses

So ....to make this job something I can actually do with that many workbooks

I'm trying to find a Macro that will


open the workbook (either all worbooks in a specified folder, or some sort of option where I can select the folder that has the workbooks)
Print a specific page ie. Page1 (preferably as a PDF - BUT I can set the Default printer to PDF so as long as I can print a specific page I think that might help)
Hide a set area of columns (Ie Columns E - R )- This will change per week for each weeks attendance (So I will manually change the Code and the columns that need hiding)
Save the workbook
Close

(Then repeat this same process for each workbook in the folder)

I honestly don't know a great deal about macros so haven't tried anything yet - mainly because - the problem is - I'm not sure where to look.

If anyone can provide any help of where to get a specific macro that will do the above (or as close as possible) this would be greatly apprecated.

Even better would be something I can start with - but I'm not expecting you to do all the work so any guidance please.

Hopefully I've provided enough information but if there's anything I need to add/clarify please let me know.

Thanks in advance

Perhaps a macro like this?

This macro lets the user select a folder, it then loops through all files in this folder and if the file type is xlsx it will open this file and activate Sheet1 and save the used range as a pdf file to a folder i.e. “C:\Temp”

Changes that should be made depending where the data you are located i.e. Sheet1 or a sheet with a specific name. If workbook contains only 1 sheet this will be the active sheet when workbook is opened.

You may probably need to change the range setting and as I have no access to a printer you should record a macro when selecting a range and print that as a pdf document.

Post this macro and then it could be added to this macro.

Please Login or Register  to view this content.

Alf

Update - just tried 20 workbooks

And works perfectly

Thank for feedback and rep

Should I post the final code for others? in case anyone ever has the same problem/query????

Yes please because I would also like to see the "print pdf part" as I don't have access to a printer.

If this solves your problem don't forget to mark this thread "Solved"

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

You are welcome and thanks for the "print pdf part".

Good to know and a thing one could suggest with a warning that it's not sure it would work on every printer.

Alf