Help with Excel VBA in Copy and Paste Values Only, not the formulas

[Nestor_Ike]

Hello,

The current code copies and paste the results with formulas. Then, it gets really tedious to export the ONLY the values, not the formulas. See the below 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

 

[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 <acronym title="visual basic for applications">VBA</acronym> code. Please help.

Thanks,
Nes