Paste Values into new worksheet for all Open Worksheets including pictures

Question

I am trying to create a renamed copy of all active workbooks (even non macro-enabled ones) without formulas, possibly by pasting values but without modifying images. I am working with Excel 2007.

My process would ideally be to:

1) Create a do while there are xls files loop that converts all xls files to xlsm. One possible addition would be an array to store A)the worksheet name(s) B)Its tabs name and their status

2) Run a for each or for loop that automatically pastes values for all active worksheets include those with graphs or other images into a new document that has the same name with all small addition at the end.

3) Convert my newly-named files containing values only into xls.

One issue I am running into when I try to do this has to do with links. The initial worksheets have formulas with links that do not automatically update. When I do this, the formulas in the original worksheet with link references tend to get corrupted.

Here is a general macro I found for pasting values:

Sub test()
Dim MyNames As Range, MyNewSheet As Range

    Set MyNames = Range("R5").CurrentRegion ' load contigeous range into variable
    For Each MyNewSheet In MyNames.Cells    ' loop through cell children of range variable
        Sheets.Add.Name = MyNewSheet.Value
    Next MyNewSheet
    MyNames.Worksheet.Select                ' move selection to original sheet
End Sub

Dave Cook Dave Cook · Accepted Answer · 2017-02-15T21:34:16

I think this is what you're asking for:

Sub test()

Dim wb As Workbook
Dim ws As Worksheet
Dim counter As Integer
Dim filePath As String

Set wb = ActiveWorkbook
countet = 1

filePath = "c:/" 'Enter your destination folder here

For Each ws In wb.Sheets
    Sheets("Sheet1").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    ActiveWorkbook.SaveAs filePath & counter & ".xlsx", FileFormat:=51
    counter = counter + 1
Next ws
End Sub

This is mostly taken from here.

The counter is a bit of a hack to make sure that the files aren't all being saved as the same name and overwriting each other. Maybe there's a more appropriate way that you can get around this.

Paste Values into new worksheet for all Open Worksheets including pictures

1 Answers