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

 

[VoG]

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

 

[MartinL]

Thanks Peter

That worked better than I hoped as It also retained the formatting.
Thanks so much.

Martin

 

[MartinL]

Hi

I'm sure Peters' code above worked.
But for some reason it doesn't. whether it was me
or something else has caused it to mess up
or it didn't do what I thought it did, I'm not sure

Basically I need "Values only" to be copied as the formulas become meaningless after the copy to the new drive.

At the moment the tab is copied to the correct place but with all the formulas, so if a re-calc takes place it all goes pear shaped!

Hope someone can help with this.

Martin

 

[VoG]

In what way isn't it working? I just tried a quick test

Sub test()
Sheets("Sheet1").Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With
End Sub

which created a new workbook with a copy of the sheet sans formulas but retaining the values.

 

[MartinL]

Hi Peter

For me, it copies the tab Worksheets("gross Sales Value")
But with all the formulas not as values only.

Let me try a test sheet and come back to you. Ill let you know what happens.

Martin

 

[MartinL]

Hmm Ok

I created a sheet with a single formula on it "=rand()" saved it on my desktop
I added your short code

Sub test()
Sheets("Sheet1").Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With
End Sub

which worked as expected

so something else is happening that is causing it to copy over the network but with the formulas.

Martin

 

[MartinL]

Hi Peter I have created the same simple sheet to copy over the network and that too works fine.

Now I'm really perplexed.

Martin

 

[Norie]

Martin

Don't use ActiveSheet.

What you regard as the active sheet might not be what VBA does.

Try fully qualifying everything.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wbNew As Workbook
 
    Application.DisplayAlerts = False
 
    Worksheets("Gross Sales Value").Copy
 
    Set wbNew = ActiveWorkbook

    With wbNew
    
        With .Worksheets(1).UsedRange
            .Value = .Value
        End With
 
        .SaveAs "L:\Performance Data\UK Sales\Sales (Latest).xls"
 
        .Close True
 
    End With
    
    Application.DisplayAlerts = True
 
End Sub

 

[MartinL]

Thanks guys

Just to keep you up to date
I created a copy of the live sheet
when I closed this copy the code ran perfectly.
I'm not sure what this tells you!!

Norie, I'll try your idea, and let you know if it makes a difference.

Martin