VBA copy active worksheet as values only

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Try

Rich (BB code):
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
 
Upvote 0
Thanks Peter

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

Martin
 
Upvote 0
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
 
Upvote 0
In what way isn't it working? I just tried a quick test

Code:
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.
 
Upvote 0
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
 
Upvote 0
Hmm Ok

I created a sheet with a single formula on it "=rand()" saved it on my desktop
I added your short code
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
 
Upvote 0
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
 
Upvote 0
Martin

Don't use ActiveSheet.

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

Try fully qualifying everything.
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top