How to copy worksheet to new workbook with data only

[dbarttx]

I'm working with Excel Office 365. (windows 10)
I've been asked to streamline a scheduling process using Excel. Once the schedule is complete that "sheet" will need to be sent to employees and client. Here's what I would like to do...

1. Create a workbook with 3 "sheets":

• Requirements,
• Employee List and Availability,
• Schedule

2. The Schedule sheet would pull information from Requirements sheet and use Drop Down lists (which come from the Employee sheet) and other conditional highlighting to help us avoid errors.

My question is: Can I/how can I export the "Schedule" sheet to another workbook where it contains only the final data and not formulas, drop downs, etc? This ultimately would be the file that gets sent to employees and the client.

Many thanks for your help!!

 

[JoeMo]

Welcome to the forum!

Assuming you have only formulas and data validation to deal with in your Schedule sheet, you could try this on a copy of your workbook (untested by me).

First complete any updates to the master workbook and save it. Then run the routine below:

Sub ScheduleDataOnly()
Dim c As Range, S As String
'convert all formulas to values
With Sheets("Schedule").UsedRange.Cells
    .Value = .Value
End With
'remove data validation from all cells
For Each c In Sheets("Schedule").UsedRange
    On Error Resume Next
    S = c.Validation.InputMessage
    If Err.Number = 0 Then c.Validation.Delete
    On Error GoTo 0
Next c
'copy Schedule to new workbook
Sheets("Schedule").Copy
'add some code here to name and save the new workbook just created with the static schedule
' then close the master workbook without saving
End Sub

 

[dbarttx]

Hi Joe,
I actually did need some "cosmetic" formatting, so I created another Macro to do that and then copied/pasted it into the code you gave me. Worked like a charm!

Thank you, thank you, thank you!!

dbarttx

 

[JoeMo]

Hi Joe,
I actually did need some "cosmetic" formatting, so I created another Macro to do that and then copied/pasted it into the code you gave me. Worked like a charm!

Thank you, thank you, thank you!!

dbarttx

You are welcome - thanks for the reply.