Creating a new workbook from a worksheet that contains formatting and pictures.

[GlenShep]

I've used Excel to create a group summary page worksheets in a workbook file that is a combination of data pulled from and calculations based on data from about 5 spreadsheets. The summary page(SP) is heavily formatted and uses merged cells extensively(customer requirement). This SP also contains several small sections of scanned PDF documents that were copied from the entire scanned documents using the windows snipping tool.

My goal is to pull the SP worksheet out from the multi worksheet workbook and write all the values into another workbook with the same name as the worksheet in the original file. By first doing a copy and paste values followed by a copy and paste formats, I am able to create the another workbook with all the funky formatting required by my client.

Unsolved problems:

1. I created a macro that does this but will only work once because I am unable to control the name of the new workbook file that is created and after the first one, VBA's choice of a new workbook name causes problems. I do rename the file to the new name but several steps are required and the macro still fails on the second and subsequent uses. The VBA code is here:

Workbooks.Add
Windows("New Abstract - Production-Redux.xlsm").Activate
Cells.Select
Selection.Copy
Windows("Book3").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

​

If I could choose the name of the workbook created by Workbooks.Add, I'd be past that problem. "book3" is the culprit. I can't choose the file name.​

2. There are either 2,3 or 4 pictures in the worksheet that I'd like to copy to the workbook. The destination ranges are fixed, known in advance. Is there an error return from Section.Copy or can I accomplish all the picture copying(pictures 1-4) in one statement that would work with however many pictures are present? Can I check for the existence of a picture?

I realize all of this is in a reference file or book somewhere but where?

I have Excel 2007 and Excel 2013 on my computer. By being careful, these two don't collide.

Thanks,

Glen