Copy all but without formulas including charts on worksheets to another workbook

[Faysal Farooqui]

Hello all.

I'm Faysal Farooqui. I'm working in a private company. I've to work whole days long with Microsoft Excel. Many of my workbooks with many worksheets I have to share with management. The Management do not need formulas. So, I share them workbooks without formulas (Copy+Value Paste) but keeping cell formatting. After adding few Charts on few Worksheets, It becomes difficult and time consuming to me:

- to copy each worksheet one by one,
- keep source formatting
- value paste
- copy charts and change it's source data

For clear understanding:
My workbook:
- Workbook_1 : contains 4 worksheets with cell formatting, charts on worksheets, formulas

My worksheets of Workbook_1 :
- ABC1 : contains sales report with Charts on Worksheet (Not Chart Sheet)
- ABC2 : contains sales report with Charts on Worksheet (Not Chart Sheet)
- ABC3 : contains sales report only
- ABC4 : contains sales report only

Now my problem is I want to apply a VBA Code that will auto generate many of my reports like above, that will copy entire Worksheets into other Worksheets of a new Workbook without formulas, keeping cell formatting and changing the charts source.

It would be a great help for me. Please pardon me if i have done something wrong.

 

[DanteAmor]

Try this.
The following creates a new book with all the sheets, just save the new book.

Sub Copy_all_without_formulas()
  Dim sh As Worksheet
  Application.ScreenUpdating = False
  Application.CopyObjectsWithCells = True
  Sheets.Copy
  For Each sh In Sheets
    sh.UsedRange.Value = sh.UsedRange.Value
  Next
End Sub

 

[Faysal Farooqui]

It works ? thanks for the magical help.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Fazza]

What about, Faysal, if you provided the file as pdf. Would that be OK? Just use keys ALT-F-H-E-F