Copy Excel active worksheet and paste into new Workbook as values

[nashnush]

Hi everyone

As a newbie I have run into the following VBA problem - I'd greatly appreciate any help you may have with this:

I am trying to use a macro to copy the active worksheet into a new workbook, then save that new workbook and close it. This is triggered by clicking on a form (button) in the active worksheet that I am in. The button is then removed in the new workbook prior to saving.

Since I am using formulas (lots of indirects) in the active worksheet, I am trying to copy not the formulas, but only the values (and any additional formatting.)

The problem is that the new workbook does not show the values, but instead only empty cells (no formulas are shown either, which is of course ok).

Thanks for any help on this one!

Nick

Here's the code:


Sub CopyRemoveFormAndSave()
Dim RelativePath As String
Dim shp As Shape
Dim testStr As String

' Copy and Paste Active Sheet
ActiveSheet.Copy
With ActiveSheet.UsedRange
.Value = .Value
End With

' Remove forms
For Each shp In ActiveSheet.Shapes
If shp.Type = 8 Then
If shp.FormControlType = 0 Then
testStr = ""
On Error Resume Next
testStr = shp.TopLeftCell.Address
On Error GoTo 0
If testStr <> "" Then shp.Delete
Else
shp.Delete
End If
End If
Next shp

' Save New Workbook and Close
Application.DisplayAlerts = False
RelativePath = ThisWorkbook.Path & "\" & ActiveSheet.Name & "_Reporting_" & Format(Now, "yymmdd") & ".xlsx"
ActiveWorkbook.SaveAs Filename:=RelativePath
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub