VBA copy active worksheet as values only

[MartinL]

Hi

I have been using the following code to save a worksheet onto a new drive whenever the workbook is closed.

I want to modify this VBA

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.DisplayAlerts = False
    Worksheets("Gross Sales Value").Copy
    Set wbNew = ActiveWorkbook
    wbNew.SaveAs "L:\Performance Data\UK Sales\Sales (Latest).xls"
    wbNew.Close True
    Application.DisplayAlerts = True
    
End Sub

So it make a values only sheet. Is this possible?

Martin

 

[MartinL]

Norie/Peter

Thanks for your help

I changed the code to Nories idea and it now works again.
I say "again", as I'm sure it worked when I left it last time using Peters code.

Thanks Guys

Martin

 

[kentck86]

Hi Guys,

i did some modification where i wanted to copy numbers of worksheet and paste as value only, but with the following VB it will copy along the formulas.

Sub Result()
Dim wbNew As Workbook
Application.DisplayAlerts = False
Worksheets(Array("Summary", "LNBTS", "LNCEL", "LNHOIF", "LNHOW", "IRFIM", _
"UFFIM", "LNADJ", "LNADJW", "RNFC", "WCEL", "HOPL", "ADJL")).Copy
Set wbNew = ActiveWorkbook


With wbNew

With .Worksheets(1).UsedRange
.Value = .Value
End With

.SaveAs "C:\Result.xlsx"



End With

Application.DisplayAlerts = True


'
End Sub

 

[Norie]

You are copying 13 sheets and only replacing formulas with values in the first worksheet.

 

[kentck86]

You are copying 13 sheets and only replacing formulas with values in the first worksheet.

Hi,

How do the VB looks like for copying all sheets? Does it looks like following:

Sub Result()
Dim wbNew As Workbook
Application.DisplayAlerts = False
Worksheets(Array("Summary", "LNBTS", "LNCEL", "LNHOIF", "LNHOW", "IRFIM", _
"UFFIM", "LNADJ", "LNADJW", "RNFC", "WCEL", "HOPL", "ADJL")).Copy
Set wbNew = ActiveWorkbook


With wbNew

With .Worksheets(13).UsedRange
.Value = .Value
End With

.SaveAs "C:\Result.xlsx"



End With

Application.DisplayAlerts = True


'
End Sub


thank you

 

[Michael M]

Maybe this UNTESTED

Sub mykl1()
Dim wbNew As Workbook, ws As Worksheet, myarray As Variant
Application.DisplayAlerts = False
myarray = Array("Sheet1", "Sheet2", "Sheet3")
Set wbNew = ActiveWorkbook
For Each ws In Sheets(myarray)
With ws
.UsedRange.Value = .UsedRange.Value
End With
Next ws
wbNew.SaveAs "C:\Result.xlsx"
Application.DisplayAlerts = True


'
End Sub

 

[chazw]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.DisplayAlerts = False
    Worksheets("Gross Sales Value").Copy
   With ActiveSheet.UsedRange
        .Value = .Value
    End With
    Set wbNew = ActiveWorkbook
    wbNew.SaveAs "L:\Performance Data\UK Sales\Sales (Latest).xls"
    wbNew.Close True
    Application.DisplayAlerts = True
    
End Sub

4 years hence and still paying dividends.

Thank you.

 

[Adamj]

Hello - How can you create a macro to select a date range from a slicer. For example i want t h emacro to select the past seven days from today's date. Thanks you

 

[Michael M]

@Adam
This is a very old post.
I'd suggest creating a new thread to get maximum exposure for your question !

 

[Adamj]

Thank you Mike i just did.