Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet and strip all vba codes
Hi all :) I have two problems....... (1) A formula or sheet to move or copy contains the name 'wrn.222' which already exist on the distination worksheet. Do you wnat to use this version of the name ? (yes or No) I donot want this msg to appear? Is there a solution ..... (2) when pressing any buttun, surbrisingly, the codes are excuted... MyMacro should strip all VBA codes, I closed the Excel application and reopen it again, open the test file, surbrisingly, when i press code bottun it opens the original fil (where the codes located) and excute them (!!all codes should b removed) here is the code: PHP code ------------------- Sub MyMacro() Dim wbActiveBook As Workbook Dim oVBComp As Object Dim oVBComps As Object Dim na As Variant With Application .ScreenUpdating = False .EnableEvents = False .AskToUpdateLinks = False .Calculation = xlCalculationManual End With On Error Resume Next 'where to put the selected sheet If Application.FindFile() Then 'Activate the main file that contain the required sheet Windows("Copy of 1 Rbuilded.xls").Activate Sheets("OrderForm").Select ' Copy to Distintation file Sheets("OrderForm").Copy Befo=Workbooks("CopyWorksheet.xls").Sheets(1) 'Rename the coopied sheet na = Application.InputBox("Write a Name For the OrderForm", "Name The OrderForm") Sheets("OrderForm").Name = na Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Workbooks("CopyWorksheet").Activate Set wbActiveBook = ActiveWorkbook 'Now strip all VBA, modules, userforms from the copy Set oVBComps = wbActiveBook.VBProject.VBComponents For Each oVBComp In oVBComps Select Case oVBComp.Type Case 1, 2, 3 'Standard Module, Class Module, Userform oVBComps.Remove oVBComp Case Else With oVBComp.CodeModule 'Worksheet or workbook code module .DeleteLines 1, .CountOfLines End With End Select Next oVBComp ActiveWorkbook.Save End If With Application .ScreenUpdating = True .EnableEvents = True .AskToUpdateLinks = True .Calculation = xlCalculationAutomatic End With End Sub ------------------- Thank yo -- helmekk ----------------------------------------------------------------------- helmekki's Profile: http://www.excelforum.com/member.php...nfo&userid=693 View this thread: http://www.excelforum.com/showthread.php?threadid=27686 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If 0, count various codes in a range on another sheet | Excel Worksheet Functions | |||
Codes needed to copy same one row from more than 300 Excel files | Excel Discussion (Misc queries) | |||
Strip out text copy and paste? | New Users to Excel | |||
What is the quickest way to copy codes in Sheets? | Excel Programming | |||
What is the quickest way to copy codes in Sheets? | Excel Programming |