Copying sheets from multiple workbooks into a master workbook

[cw1]

HI Guys i'm hoping one of you will be able to help me with the macro i'm trying to create as I have no idea what I am doing!

I have multiple workbooks all contained in one folder which all have a sheet titled "Summary".
I have another workbook which I would like to pull all the "summary" sheets into (if possible values and format) and change the name of the sheet to the contents of one of the cells in the sheet plus the word summary eg cell B2 = Widget therefore sheet name should be "WidgetSummary" So I will end up with the master file which already has 2 sheets in it plus a number of summary sheets all pulled from different workbooks which are stored in 1 folder.

I hope that makes sense, any help would be really appreciated.

 

[Andrew Poulsom]

The only circumstance in which the sheet isn't renamed is if that name isn't valid (ie an error is generated). Of course there could be a number of reasons for that, including duplicate names. If you want to see what is causing the error comment out:

On Error Resume Next

 

[cw1]

Hi Andrew,

I'm not quite sure how I see what is causing the error (I am very new to this). But looking at it it seems to be the files with longer names which are not working, is this likely to be the issue? Going completely off topic would you suggest getting a book and trying to self teach VB or would you suggest finding a course instead?

Thanks again for all your help!

 

[Andrew Poulsom]

There is a maximum length of 31 characters.

I like John Walkenbach's books:

http://www.spreadsheetpage.com/index.php/book/C5

 

[cw1]

Hi Andrew,

I've renamed the files and it all works now, thank you very much for your help quite clearly I couldn't have got it working without you!

I will have a look at that book you recommend as I would be very interesting to learn some VB.

Thanks Again!