Copy range to a new workbook without formulas

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Copy range to a new workbook without formulas

Post by adam »

hi anyone,

I'm using the following code which copies the active worksheet to a new workbook.

It copies and pastes the whole sheet range. I have the sheet named "invoice" hidden.

How could I make the code to copy and paste the range A1:AR27 from the sheet invoice to a new workbook without copying the formulas?

Code: Select all

Sub CopySheetToClosedWB()
Application.ScreenUpdating = False
 
Cells.Select

Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.Dialogs(xlDialogSaveAs).Show
 
Application.ScreenUpdating = True
End Sub
Any help would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy range to a new workbook without formulas

Post by HansV »

For example:

Code: Select all

Sub CopySheetToNewWB()
    Application.ScreenUpdating = False
    Worksheets("Invoice").Range("A1:AR27").Copy
    Workbooks.Add Template:=xlWBATWorksheet
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Application.Dialogs(xlDialogSaveAs).Show
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Copy range to a new workbook without formulas

Post by adam »

Thanks for the help Hans.

But I'm getting the error "PasteSpecial method of Range class failed". highlighting the line; Selection.PasteSpecial Paste:=xlPasteValues

How could I overcome this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy range to a new workbook without formulas

Post by HansV »

I have tested the code, it runs without error.
Please attach a sample workbook (without sensitive date) that demonstrates the problem.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Copy range to a new workbook without formulas

Post by adam »

Thanks for the help. It worked fine.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Copy range to a new workbook without formulas

Post by adam »

As mentioned earlier the code works fine. However, it changes the width of the columns when it gets saved to the new workbook. How can this be solved?
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy range to a new workbook without formulas

Post by HansV »

You can add a line to paste the column widths:

Code: Select all

Sub CopySheetToNewWB()
    Application.ScreenUpdating = False
    Worksheets("Invoice").Range("A1:AR27").Copy
    Workbooks.Add Template:=xlWBATWorksheet
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Selection.PasteSpecial Paste:=xlPasteColumnWidths
    Application.Dialogs(xlDialogSaveAs).Show
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Copy range to a new workbook without formulas

Post by adam »

Thankyou Hans. It worked well.
Best Regards,
Adam