Export Excel sheet to another Excel sheet.

[ebeyert]

All,
I have the following macro, which export the active worksheet to a new worksheet (new excel workbook/sheet “book 1”) (without the formulas, but with the columns width, values and formats.
My only issue is that I also have a picture in the active worksheet which don’t be copied to the new worksheet.

Can someone assist me with this?


Current Marco:

Public Sub ExcelExport()

Application.GoTo Reference:="Print_Area"
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub


Thanks in Advantage,
Best regards
Ellerd

 

[xladept]

Hi Ellerd,

How about:

Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste

 

[ebeyert]

Hi Ellerd,

How about:

Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste

Thanks for your reply. I added the "ActiveSheet.Paste" to my macro and now i see that the picture is also copied, but i get a runtime error '1004" with the following message: This operation requires the merged cells to be indetically sized."

Public Sub ExcelExport()        
    Application.GoTo Reference:="Print_Area"
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

[/QUOTE]


If i use only your macro

Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste

[/QUOTE]


Then indeed the picture is copied but not the ColumnWidths into the new sheet..

 

[xladept]

Hi Ellerd,

If there is nothing else on your export sheet- you could try this:

Cells.Copy 
    Workbooks.Add     
ActiveSheet.Paste