Copy worksheet to new tab..... with a twist

picklefactory

Well-known Member
Joined
Jan 28, 2005
Messages
508
Office Version
  1. 365
Platform
  1. Windows
Hi folks
I found the below code on here (Thank you Steve the Fish) to copy a worksheet and rename..... works a treat, no problem there. However, I now wish to complicate matters (Like wot I always do). Currently it simply duplicates the sheet to a new sheet and renames as I wish from the value in a specified cell, but 3 of the cells contain a formula. Is it possible to have it copy the sheet but with cell values only, and not the formulae? It would be fine to have the entire sheet copied as values only, if that makes it easier, otherwise it's just cells C15, C17 and C19 that contain the formulae I don't want copied.
Is that tricky?

Here's the code
VBA Code:
'Copy sheet to new tab
Dim wks As Worksheet
Set wsTem = Sheets("Employee List")
Set wsMain = Sheets("Input")

If Not SheetExists(wsTem.Range("J9").Value) Then
    wsMain.Copy after:=Worksheets(Sheets.Count)
    ActiveSheet.Name = wsTem.Range("J9").Value
    wsMain.Activate
End If
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Oh, sorry... I just managed to find the solution. I just added the below into the function and does what I want....... dead easy, this VBA stuff ;)o_O

VBA Code:
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

So it now becomes
VBA Code:
'Copy sheet to new tab
Dim wks As Worksheet
Set wsTem = Sheets("Employee List")
Set wsMain = Sheets("Input")

If Not SheetExists(wsTem.Range("J9").Value) Then
    wsMain.Copy after:=Worksheets(Sheets.Count)
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    ActiveSheet.Name = wsTem.Range("J9").Value
    wsMain.Activate
End If
 
Upvote 0
Solution

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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