Macro to copy spreadsheet and graph on new workbook values only

Hi everyone,

I would like to do what I think would be a simple thing for you VBA geniuses but very difficult for me to say the least.

I have attached a very basic example to capture what I'm trying to achieve. I have a massive workbook on which I track inventory via figures and graphs (on separate spreadsheets). My aim is to have a macro (that I'd link to a shape) to copy the highlighted sections in yellow as well as the graph from the other spreadsheet on a new workbook. The layout needs to be slightly different as it will be sent to different users who shouldn't have access to all the info available in the original workbook.

I would like all the values to be copied as "values only" and the graph as a bitmap. Also, there will probably be conditional formatting in columns B, C, D, G and H mainly dependent on how small or high the figures are. In a perfect world, I would also like to copy this formatting across.

The end result (which would need to be on a different workbook) is on the spreadsheet called "Target Layout", including the frozen pans and background colors.

I've managed to do something very dodgy with the macro recorder so I would assume it could work but I definitely some help.

Anyone's help on this would be massively appreciated :-D

Sub

Example inventory.xlsx

Hi everyone,

I managed to get a few things done but now I have a subscript range error:

Sub Test()
'
' Test Macro
'
    Set NewBook = Workbooks.Add
    Do
    fName = Application.GetSaveAsFilename
    Loop Until fName <> False
    NewBook.SaveAs Filename:=fName

    Cells.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
    Windows("Example Inventory Macro.xlsm").Activate
    Range("B1:D19,G1:H19").Select
    Range("G1").Activate
    Selection.Copy
    Windows("Book1").Activate
    Range("B17").Select
    ActiveSheet.Paste
    Range("B19:F35").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:F").Select
    Range("B13").Activate
    Columns("B:F").EntireColumn.AutoFit
    Rows("16:16").Select
    ActiveWindow.FreezePanes = True
    Windows("Example Inventory Macro.xlsm").Activate
    Sheets("Graph").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    Application.CutCopyMode = False
    ActiveChart.ChartArea.Copy
    Windows("Book3").Activate
    ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:= _
        False
End Sub

I assume it is because Windows("Book1").Activate does not refer to the actual file I have just saved but I don't know how to make it capture the file name I just entered.

The rest of the code seems to be reasonably ok (still working through a few things) if I have a file with the appropriate name but I need to find a work around.

Any guess from anyone?

Thanks,

S