ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   New WorkBook Macro Looking to WRONG WorkBook (https://www.excelbanter.com/excel-programming/365764-new-workbook-macro-looking-wrong-workbook.html)

Corey

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....


All times are GMT +1. The time now is 01:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com