VBA copy active worksheet as values only

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows
Thanks Peter

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

Martin
 

MartinL

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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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.
 

MartinL

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

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

MartinL

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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
 

MartinL

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

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

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
Top