Consolidation of info. Files differing locations, password protected, create one summary

[marsham]

I am very new to Macro's and have managed with the help of this site to do a few simple things, with a little tweeking.
I am now trying to piece together a macro to do what I would imagine would be quite common but I am taking two steps forward & three back.

I have multiple worksheets used by many people - they are in the same drive but different folders.
On these workbooks there are multiple worksheets - but I want to summarise a named page (same name) from each
As in the originals there is a lot of formulation & look up tables I have password protected the worksheets & workbook.
The data in the sheets that are to be summarised will vary greatly in data from month to month.

As the summaries are to be created by another user, what I need a macro to do is -
Go into Excel - open new book & run Macro - this will in turn
Open the appropriate file, unprotect the specifically named (w/book & w/sheet)
Copy all the cells with data in them
Paste Special (Values and Formatting)
Protect the specifically named sheet & close the file
Move onto the next & repeat the process BUT copy the information to the next available blank row
* A bonus given that this relates to a giant list with monthly info would be - that when the macro is run it asks what month's info you want to copy (always in column B) and then only copies that. Otherwise come mid year more time will be spent deleting copied information.

I have tried recording these steps (from what I know) & then combining them with no luck.

I am not sure how to get it to,
Automatically open a file - I've been opening them manually
How to automatically unprotect one worksheet in the file (without the user knowing the password)
Coping only populated cells
Pasting (special) to the next available blanks on the same page
Automatically protecting & closing the original worksheet

I have attached a dummy set of files, if anyone is interested in having a look and getting me going in the right direction.
The page I am trying to copy is called "PEG" in these examples & the password on everything is "secret"

Thanks

[tehneXus]

Hi,

try this: marsham.xlsm

Adapt the file locations in the 2nd sheet, adapt the password in the code module, i assumed it is fixed.
The month is selected on the userform, the last entry is all.
Start the macro by pressing the button on the 1st sheet. Error messages are displayed in the Immediate Window.

Regards

[marsham]

tehneXus - sorry I think I must be missing something.
I have tried your file (which is looking perfect) but when I import & select the month or all - nothing happens.
I don't mean to ask silly questions - although I don't believe there is such as a thing - the only silly thing is not to ask!

Cheers

[marsham]

Sorry clearly a long day, everything is working on the file you sent.
I will get into the Actual filies & fingers crossed I won't mess anything up.

Thank you - Perfect fit

Cheers

[marsham]

tehneXus

Thanks I have managed to get it working on actual files, but when testing I have come across a few things that need tweeked or aren'tworking just 100%.
One selection just isn't working as well well as the others, with the additional instructons I'm not sure where to write them into your code & am hesitant to fiddle about with it too much. Could you perhaps point me in the right direction.

When I select April or All as the month it is copying a lot of blank cells?
Jan - Mar have worked well copying only the relevant information. Any ideas?

These files are all linked, so when you open the file it shows the message box:
This workbook contains links to other data sources. blah...blah. three boxes Update, Don't Update and Help.
I ususally just click on the close cross at the top right.

When the Macro is running it does the same thing -from my simplistic way of recording to find the language I think it would be something like:

Please Login or Register  to view this content.

THe other thing is in the pasting - as many of the cells are formulated I need it to paste very specifically.
Again basic recording shows the code as below. Is this correct & where on your Macro should I enter it.

Please Login or Register  to view this content.

Your help in this would be greatly appreciated

[tehneXus]

Hi,

When I select April or All as the month it is copying a lot of blank cells?

No, it is not copying anything, e.g. you select April but April is not listed in column 2 of the source sheet, then an error message will be given and the copy will be skipped, when you select All the program should copy all records from the source sheet. Blank records cannot be copied as the sources are sorted by column 2 prior to look for the 1st and last row of the appropriate month.

Please Login or Register  to view this content.

changed to

Please Login or Register  to view this content.

Feel free to adapt the code below the comment 'copy

Please Login or Register  to view this content.

Error messages are displayed, if any, in a MsgBox at the end instead of the Immediate window now.

marsham.xlsm

Regards.

[marsham]

Hi,

No, it is not copying anything, e.g. you select April but April is not listed in column 2 of the source sheet, then an error message will be given and the copy will be skipped, when you select All the program should copy all records from the source sheet. Blank records cannot be copied as the sources are sorted by column 2 prior to look for the 1st and last row of the appropriate month.

On my actual file it is inserting the File Name-Date-Time in columns A-C and blank cells from D-End (as there is no info)When I tested it I think it is because the actual files have a lot of formulas, lookups etc but they are only activated when a number is put in the first colum.
My original example cells were copies of info & didn't contain the formulas etc (Sorry)
I have attached another example of Example 1 to show, it doesn't have all the formulas but should show when the macro is run what I mean.
marsham.png


Additionally as I think how this will be used - is there a way if the Macro is ran selecting for example March, that it will create a worksheet, name it March & save it as another tab instead of populating the Master sheet?

I do apologise for all the follow up questions due to providing insufficient information - I am new to this & your language is certainly far more advanced than my teach yourself knowledge.

Would appreciate any assistance you could offer.

[tehneXus]

Hi,

1. Would've been great to have all the information at the very beginning, keep this in mind for further threads here

2. I noticed that you are using ALL available columns in your Example file, means 16384 columns. If this is the case in all your files we will have to drop the 3 columns of the Master sheet. So drop them?

3. Empty cells will no longer be copied.

4. If you do not want cell colors to be copied inform me.

5. Regarding the creation of a new worksheet for a specific month:
5.1. What happens if the user selects "All"?
5.2. What happens if the user selects "March" and the worksheet "March" already exists? Append the data or replace it or do nothing and give an error message?

Regards.

[marsham]

Hi,

1. I am really sorry for not providing all information, I suppose with my 'beginner status' I didn't really understand things that might make a difference & I was just trying to do one step at a time, so didn't create my example files as detailed as the actuals. I will bear this in mind for future.

2. I won't need the 16384 columns. I think the way I locked the cells I just highlighted everything to be on the safe side & unlocked the approriate cells. I would only need columns A-Z - this allows for changes in the future.

3. That would be perfect

4. The only coloured cells I need would be a conditional formatting on duplicated numbers from column A. This is something even I can do!

5. This was a bonus, as I won't be the only user of this & the I am a genius in comparrison to the other user!
I suppose in my mind if they selected ALL - it would name it ALL
IF they had already created March & reran it - it would pop create a March(2) or similar - they could then delete the w/sheets as required.
*This really isn't a neccesity - they have to learn what to do - although I have shown them many a time I think they play me a little, as they know if the mess it up I will fix it & do it from that day on!!!

I greatly appreciate your patience with me and help on this.

[tehneXus]

Hi,

try this: marsham.xlsm

Regards

[marsham]

Hi,
Can't seem to find a Gold Star on here, otherwise I would have been sending a few.
It is working perfectly & does everything & more.

Now I will have to go away to try & understand how you do it?!?

Your patience with me to get the prefect result is greatly appreciated, even though I kept changig the goal posts.

Thanks Again