Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-03-2019, 01:35 AM
Bumba Bumba is offline How to clone a worksheet with udf in excel 2007? Windows 7 32bit How to clone a worksheet with udf in excel 2007? Office 2010
Novice
How to clone a worksheet with udf in excel 2007?
 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default 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 ?
Reply With Quote
  #2  
Old 11-03-2019, 02:36 PM
Logit Logit is offline How to clone a worksheet with udf in excel 2007? Windows 10 How to clone a worksheet with udf in excel 2007? Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
You can edit the path for your purposes.
Reply With Quote
  #3  
Old 11-03-2019, 02:39 PM
Logit Logit is offline How to clone a worksheet with udf in excel 2007? Windows 10 How to clone a worksheet with udf in excel 2007? Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Deleted .. Duplicate
Reply With Quote
  #4  
Old 11-03-2019, 09:11 PM
Bumba Bumba is offline How to clone a worksheet with udf in excel 2007? Windows 7 32bit How to clone a worksheet with udf in excel 2007? Office 2007
Novice
How to clone a worksheet with udf in excel 2007?
 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default

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?
Reply With Quote
  #5  
Old 11-04-2019, 12:35 AM
Logit Logit is offline How to clone a worksheet with udf in excel 2007? Windows 10 How to clone a worksheet with udf in excel 2007? Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Tested here and it works to save the formulas as well.

Yes.
Reply With Quote
Reply

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
How to clone a worksheet with udf in excel 2007? Clone Outlook Settings pmokover Outlook 1 08-30-2010 12:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft