new workbook needs to copy formulas and values not just values

[winwall]

Hi guys,

what do i need to change in this code to have the new workbook copy over formulas and formatting, not values. if someone can advise that would be great

Mark

'create weekly sheet for individual store

Option Explicit
Const fPATH As String = "C:\users\mark\desktop\"     'remember the final \ in this string
 
Sub ExportActiveSheet()
Dim wbNEW As Workbook
 
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
ActiveSheet.Copy
Set wbNEW = ActiveWorkbook
 
With wbNEW.Sheets(1)
    .Range("A1:Z200").Value = .Range("A1:Z200").Value
    .Range("").Hidden = True
    wbNEW.SaveAs fPATH & "Weekly Sales - " & .[B4].Value & ".xlsx", 51
End With
 
wbNEW.Close False
Application.ScreenUpdating = True
MsgBox "Saved"
End Sub

[Rioran]

Good time of day, WinWall!

As I see, your code fills new sheet with it's own values to annihilate formulas. I think if you will just replace this row, you may get what you want:

.Range("A1:Z200").Value = .Range("A1:Z200").Value

[winwall]

i just played with it and replaced the .value with formulas on that row and it worked like a charm.

Mark