How to Fix Graphs not copying over to new sheet with PasteSpecial

Question

upon button press, I have a VBA Macro that copies the information from my active sheet, opens up a new workbook and pastes the copied data into "sheet1". When I use the "ActiveSheet.Paste" command, all text and graphs copy over, but the column widths do not. When I use "PasteSpecial", the text and proper column widths transfer over, but none of the graphs do.

See code below:

The code below copies all of the text and graphs, but doesn’t paste column widths so the result is really ugly

Range("A1:W500").Select
Selection.Copy 'copies the range above
Windows(NewWorkBookName).Activate 'activates the new workbook again
ActiveSheet.Paste

The code below pastes the proper column widths, but not the graphs.

Sheets("Dashboard").Range("A1:Z500").Copy
Windows(NewWorkBookName).Activate
With Sheets("Sheet1").Range("A1")
        .PasteSpecial xlPasteAll
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteAllUsingSourceTheme
    End With
    Application.CutCopyMode = False

Any idea what is going on, and how I can fix this? THANKS!!

Use a regular copy/paste then copy/paste special for the column widths. — Tim Williams, Feb 01 '19 at 06:35
Tim, how would this be accomplished? Copy/paste a blank row only using pastespecial? Seems like there should be a solution to copy everything. — TechJunkie, Feb 01 '19 at 06:42
You can copy any row - you're only pasting the columnwidths, not any content. Or just copy the whole sheet to create a new workbook. — Tim Williams, Feb 01 '19 at 06:46
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Feb 01 '19 at 07:23

simple-solution · Accepted Answer · 2019-02-06T09:02:30.550

Option Explicit

Sub Copy1()
'code sample 1 from OP:
'The code below copies all of the text and graphs, but doesn’t paste column widths so the result is really ugly
Range("A1:Z500").Copy
Workbooks.Add           'adds new workbook
ActiveSheet.Paste
End Sub

Sub Copy2()
'code sample 2 from OP:
'The code below pastes the proper column widths, but not the graphs.
Range("A1:Z500").Copy
Workbooks.Add           'adds new workbook
With ActiveSheet.Range("A1")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteAllUsingSourceTheme
End With
Application.CutCopyMode = False
End Sub

Sub Copy3()
'the regular copy + copy ColumnWidths approach (proposed by Tim Williams)
Dim oldWb As Workbook
Dim oldWs As Worksheet
Dim newWb As Workbook

'regular copy => does not copy column width
Set oldWb = ActiveSheet.Parent
Set oldWs = ActiveSheet
Range("A1:Z500").Copy
Set newWb = Workbooks.Add           'adds new workbook
ActiveSheet.Paste

'copy columnwidths => does not copy graphs
oldWs.Range("A1:Z500").Copy
With ActiveSheet.Range("A1")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteAllUsingSourceTheme
End With
Application.CutCopyMode = False
End Sub

Sub Copy4()
'if the full column is selected the column width and graphs are copied to the new sheet
Range("A:Z").Copy
Workbooks.Add           'adds new workbook
ActiveSheet.Paste
End Sub

Sub Copy5()
'if the whole sheet is copied the column width and the graphs are copied
ActiveSheet.Copy        'copy activeSheet to a new workbook
End Sub

How to Fix Graphs not copying over to new sheet with PasteSpecial

1 Answers1

Linked