Copy worksheets to new workbook and prevent them changing

Question

I have a VBA loop to generate a series of dashboards. It works by changing a two letter code in one particular cell, preforming a look-up, and allowing a set of charts to update on that worksheet. So far I have been exporting PDFs for each of the refreshed dashboard, but now I need to make a static copy of that worksheet in excel.

In between swapping the codes, can I make a copy of the worksheet that updates, and save it in another workbook. I would end up generating one workbook with 20 similar worksheets. I want the charts to be frozen indefinitly.

Can I do this by preventing automatic re-calculation, or can anyone suggest another method? The worksheet itself contains 10-15 seperate charts.

Answer 1

If I understand correctly, you want to copy the current sheet to a new sheet on an existing workbook, stripping out any formulas or VBA? This should do it.

Sub transferStrip(destFile As String)
    Dim wbSource As Workbook
    Dim wbDest As Workbook
    Dim shCopy As Worksheet
    Dim shPaste As Worksheet
    Dim chtIndex As Integer

    Set wbSource = ActiveWorkbook
    Set wbDest = Workbooks.Open(destFile)
    Set shCopy = wbSource.Sheets("[SheetName]")

    shCopy.UsedRange.Copy
    Set shPaste = wbDest.Sheets.Add(After:=wbDest.Sheets(wbDest.Sheets.Count))
    shPaste.Range("A1").PasteSpecial (xlPasteFormats)
    shPaste.Range("A1").PasteSpecial xlPasteValues

    chtIndex = 1
    For Each ch In shCopy.ChartObjects
        shCopy.ChartObjects(chtIndex).CopyPicture
        shPaste.Paste
        shPaste.Shapes(chtIndex).Left = shCopy.ChartObjects(chtIndex).Left
        shPaste.Shapes(chtIndex).Top = shCopy.ChartObjects(chtIndex).Top
    Next ch

wbDest.Close (True)
End Sub

You will need to change to change [SheetName] to the name of the sheet you wish to copy from in the source workbook, and to pass the path of the workbook you wish to copy to as destFile.