Macro recorder help

[scrappe7]

Hello, i am trying to fine tune a part of my database monster. I have an xls file that contains links to 6 other excel files and mixes and matches what i want from each into 1 nice spreadsheet. (Takes various data and moves to one file using links).

However, since i want to preserve this file and its links i have made a macro that would open up a new worksheet, copy all the data and paste special "as values". This makes a new sheet that the user could modify if they liked and the file with the links that grabs the data stays in tact. That works fine.

The problem is in copying charts/graphs that are located on a 2nd sheet in the links file. I can copy and paste them from the 'links' to the 'book1' manually and it will work. However, if i try to automate it using a macro i get "unable to get the ChartObjects property of the worlsheet class". Another problem is the macro records the creation of a new worksheet as either book1, book2, book3... etx. depending on how many books i have previously made before closing excel ( ie it continuously counts up). So if the macro is set and book1 was created if the next time i try run the macro and excel is passed book1 i get an error. i need to set the new workbook title to something stable, or let the user pick it before the macro is run?

Here is what the macro for copying the chart looks like.

Sub test2()
'
' test2 Macro
' Macro recorded 1/4/02 by Edward
'

'
ActiveWindow.Visible = False
Workbooks.Add
ActiveWindow.WindowState = xlMinimized
ActiveSheet.ChartObjects("Chart 1025&quot.Activate
ActiveWindow.Visible = False
Windows("Quarterly Risk Summary.xls&quot.Activate
Range("P3&quot.Select
ActiveSheet.ChartObjects("Chart 1025&quot.Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
Windows("Book3&quot.Activate
ActiveWindow.WindowState = xlMaximized
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveWindow.Visible = False
Windows("Book3&quot.Activate
Range("B32&quot.Select
ActiveWindow.WindowState = xlMinimized
ActiveSheet.ChartObjects("Chart 1025&quot.Activate
Windows("Quarterly Risk Summary.xls&quot.SmallScroll Down:=18
ActiveWindow.Visible = False
Windows("Quarterly Risk Summary.xls&quot.Activate
ActiveSheet.ChartObjects("Chart 1036&quot.Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
Windows("Book1&quot.Activate
ActiveWindow.WindowState = xlMaximized
ActiveWindow.SmallScroll Down:=6
Range("A34&quot.Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 2&quot.Activate
Windows("Book3&quot.SmallScroll Down:=21
ActiveWindow.Visible = False
ActiveWindow.WindowState = xlMinimized
ActiveSheet.ChartObjects("Chart 1036&quot.Activate
End Sub


Again a stable file to paste the material, a way to paste the charts as well is needed, thanks.

Ed

 

[BenUK]

I would say this is the most efficient way:

With the graph why not simply create a workbook template which already contains the graph and paste values over the top each time. Use the below save as code and execute after the paste values procedure so you do not copy over your info each time.

Set NewBook = Workbooks.Add

NewBook.SaveAs Filename:="Workbook " & Format(Date, "dd.mm.yy&quot

 

[scrappe7]

I see what your saying but our new approach will not use the graphs anymore. Instead i would like to be able to have my macro copy the selection and then display the SAVE AS screen so the user can pick where to save the new info. This picked file will then be the same file that the data gets pasted to. the last step of the macro would close the original file. WHat i have now is this:

Sub CopyNum()
'
' CopyNum Macro
' Macro recorded 1/4/02 by Edward Santevecchi
'

'
Cells.Select
Selection.Copy
Set NewBook = Workbooks.Add
NewBook.SaveAs FileName:="Workbook " & Format
(Date, "dd.mm.yy&quot ' Prompt for output file here!!!

Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
'Tell to paste to newly saved file

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1&quot.Select


' CloseRisk Macro
' Macro recorded 1/4/02 by Edward
'

'
Windows("Quarterly Issues (9-30).xls&quot.Activate ' closes original file
ActiveWorkbook.Save
ActiveWindow.Close
With ActiveWindow
.Top = 82
.Left = 22
End With



End Sub

 

[SkipVought]

Hi,
I may be looking at this too simplistically, but here are my thots...

    Dim objSource As Workbook, objTarget As Workbook
    
    'Activate the source workbook
    SourceName.Activate
    Set objSource = ActiveWorkbook
    
    'activate the target workbook
    targetname.Activate
    Set objTarget = ActiveWorkbook
    
    For Each Worksheet In objSource
        Worksheet.Activate
        For Each Chart In Worksheet.Charts
            Chart.Copy
            
            objTarget.Activate
            'select where you want to paste the chart
            ActiveSheet.Paste
        Next
    Next

I think that this should work, or some derivative of it.
Skip,
metzgsk@voughtaircraft.com