i have a macro that in part, copies module1 and userforms1-3 to a target workbook. what i want to do now is test for module1 in the target workbook and delete it if found. same for userforms.
here is my macro.
Sub startSaveModule()
'declare the variables
Dim x As String
Dim openworkbook As Workbook
Dim FNametxt As String, FNamefrm As String, FileSelected As String
Dim FNamefrm3 As String, FileName As String, FNamefrm2 As String
Dim I As Long
With ThisWorkbook
Let FNametxt = .Path & "\AFolderForUserFormsAndCodeText\code.txt"
.VBProject.VBComponents("Module1").Export FNametxt
Let FNamefrm = .Path & "\AFolderForUserFormsAndCodeText\form.frm"
.VBProject.VBComponents("UserForm1").Export FNamefrm
Let FNamefrm2 = .Path & "\AFolderForUserFormsAndCodeText\form2.frm"
.VBProject.VBComponents("UserForm2").Export FNamefrm2
Let FNamefrm3 = .Path & "\AFolderForUserFormsAndCodeText\form3.frm"
.VBProject.VBComponents("UserForm3").Export FNamefrm3
End With
'--------------------------------
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count <> 1 Then
MsgBox "Target Workbook canceled by User, nothing done...", vbExclamation
Exit Sub
End If
Let FileSelected = .SelectedItems(1)
Let FileName = Right(FileSelected, Len(FileSelected) - InStrRev(FileSelected, "\"))
Dim FileNameNoExt As String: Let FileNameNoExt = Left(FileName, (InStrRev(FileName, ".") - 1)):
End With
Set openworkbook = Workbooks.Open(FileSelected)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For I = 1 To Worksheets.Count
On Error Resume Next
Sheets(I).Activate
Select Case ActiveSheet.Name
Case Is = "Master", "BigMaster", "Estimating1", "Navigation", "Formulas"
Sheets(I).Delete
Case Else
GoTo zz
End Select
zz:
On Error GoTo 0
Next I
With ThisWorkbook
.Sheets("Formulas").Copy Before:=openworkbook.Sheets(1)
.Sheets("Master").Copy Before:=openworkbook.Sheets(1)
.Sheets("BigMaster").Copy Before:=openworkbook.Sheets(1)
.Sheets("Estimating1").Copy Before:=openworkbook.Sheets(1)
.Sheets("Navigation").Copy Before:=openworkbook.Sheets(1)
.Activate
End With
openworkbook.Sheets("navigation").Activate
Sheets("navigation").Select
Workbooks(FileName).VBProject.VBComponents.Import FNametxt
Workbooks(FileName).VBProject.VBComponents.Import FNamefrm
Workbooks(FileName).VBProject.VBComponents.Import FNamefrm2
Workbooks(FileName).VBProject.VBComponents.Import FNamefrm3
x = "'" & FileName & "'!RunGrand"
Application.Run x
End Sub
Bookmarks