#1
|
|||
|
|||
How to clone a worksheet with udf in excel 2007?
I have some worksheets where I used some built-in functions and UDF's along with some formatting done to the sheet.
I'm trying to create a macro or something which when I run basically copies the sheet(along with its formatting, styling & cell height width etc.) to a new file and saves it to a workbook in a specific path in D drive without showing a #NAME or #VALUE or any other error and without changing the values. I'm new to VBA and stuff, can anyone help me with this ? |
#2
|
|||
|
|||
This will save each sheet in the same location as the workbook :
Code:
Option Explicit Sub save_each_sheet_as_separate_workbook() Dim wb As Workbook, ws As Worksheet Application.ScreenUpdating = False ''/////////// Include WB to be affectted in path below \\\\\\\\\ Set wb = ThisWorkbook For Each ws In wb.Worksheets If ws.Visible = True Then 'handles hidden sheets ws.Copy With ActiveWorkbook .SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name, FileFormat:=56 '56 for xls, 51 for xlsx, 52 for xlsm .Close False End With End If Next ws wb.Close False Application.ScreenUpdating = True End Sub |
#3
|
|||
|
|||
Deleted .. Duplicate
|
#4
|
|||
|
|||
Thanks Logit.
Btw if I send the extracted sheet to someone else will they be able to see the cells value where I have used udf's? |
#5
|
|||
|
|||
Tested here and it works to save the formulas as well.
Yes. |
Tags |
vba excel |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel 2007 opens with worksheet displayed | rbul1 | Excel | 3 | 01-06-2019 06:25 AM |
Excel Calendar in a worksheet | kenbird | Excel | 0 | 04-26-2015 02:48 AM |
Worksheet Object in powerpoint 2007 | sridharreddyg20 | PowerPoint | 0 | 03-06-2014 12:05 PM |
Paste special an Excel range into Outlook as an Excel Worksheet | charlesh3 | Excel Programming | 3 | 02-04-2013 04:33 PM |
Clone Outlook Settings | pmokover | Outlook | 1 | 08-30-2010 12:04 PM |