copy and paste VALUES ONLY, not the formulas.

[Nestor_Ike]

Hello,

The current code copies and pastes the results with formulas. Then, it gets really tedious to export the worksheets to a new workbook as the worksheets lose formula references. I need to be able to copy and paste VALUES ONLY, not the formulas. See the above VBA code. Please help.

Thanks,
Nes
=================
Sub CreateSheets()
Dim rng As Range
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksData As Worksheet
Dim strwks As String
Set wkb = ActiveWorkbook
Set wksData = wkb.Sheets("Loss_Calculations")
strwks = ""
On Error GoTo ErrorHandler
For Each wks In wkb.Sheets
strwks = strwks & " " & wks.Name
Next wks
'For Each rng In wksData.Range("A12:A" & wksData.Range("A" & wksData.Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
' If InStr(1, strwks, rng.Value) > 0 Then
' 'do nothing
' Else
' wkb.Sheets("AcctTemplate").Copy After:=wkb.Sheets(wkb.Sheets.Count)
' wkb.Sheets(wkb.Sheets.Count).Name = rng.Value
' End If
'Next rng
For Each rng In wksData.Range("A12:A" & wksData.Range("A" & wksData.Rows.Count).End(xlUp).Row)
If rng.EntireRow.Hidden = False Then
If InStr(1, strwks, rng.Value) > 0 Then
'do nothing
Else
wkb.Sheets("AcctTemplate").Copy After:=wkb.Sheets(wkb.Sheets.Count)
wkb.Sheets(wkb.Sheets.Count).Name = rng.Value
End If
Else: End If
Next rng
ExitClause:
Exit Sub
ErrorHandler:
MsgBox "[" & Error() & "]"
Resume ExitClause

End Sub

 

[mole999]

if you record it in macro recorder you can paste as value, and that will show you the construct of your macro