View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Corey Corey is offline
external usenet poster
 
Posts: 172
Default New WorkBook Macro Looking to WRONG WorkBook

The macro i am using to create a Copy of the Active WorkBook is:

Sub Macro20()
' This macro prompts you that MAX sheets is reached and to Name the New Copy (sheet1,sheet2)
' Range("A4").Select
' Selection.ClearContents
res = InputBox("MAXIMUM File SIZE REACHED, Enter the NAME for the NEW file ? ", "Title here...")
If res = "" Then Exit Sub
With ActiveWorkbook
Worksheets(Array("Enter-Exit Page", "1")).Copy
ThisWorkbook.Save ' <================= Refers to Original WorkBook
ThisWorkbook.Close ' <================ Refers to Original WorkBook

' <==== res to be the NAME NEW WorkBook is SAVED AS HERE

End With
ActiveWindow.DisplayWorkbookTabs = True
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = False
End With
End Sub

But, When i run another macro in the New Copy, the ORIGINAL WorkBook Opens Back Up ???

Why is that ?

Code running when this happens is:

If .Worksheets.Count < 5 Then
End If
End With
With ActiveWorkbook
..Worksheets("1").Copy After:=.Worksheets(.Worksheets.Count)
ActiveSheet.Name = Worksheets.Count
UserForm1.Hide
Dim myFileName As Variant
Dim NewWksName As String
Dim NewWkbk As Workbook
Dim NewWks As Worksheet
NewWksName = Worksheets.Count
' Me.ComboBox1.Value
With NewWkbk
Set NewWks = Nothing
On Error Resume Next
Set NewWks = .Worksheets(NewWksName)
On Error GoTo 0
End With
If NewWks Is Nothing Then
End If
[B3].Select
ActiveCell = Me.ComboBox1.Value
' Set NewWks = ActiveSheet
' With NewWks
' .Name = Me.ComboBox1.Value
' .Range("B3").Value = NewWksName
End With
End Sub


Can anyone point me in teh right direction of where to look for a solution to this, please.
regards

Corey....