How do I keep the active file open after saving copy, please?

[Lee Jeffery]

I am using Excel 97 with OS WINNT.

I have recorded a macro which copies the first sheet called "Daily
from my source workbook to a new workbook, names this new workbook "PO
Commencements (Today's date)".xls, and closes the new workbook. It i
also closing the source workbook.

I would like to keep the source workbook open after the copyin
process. Alternatively, automatically reopen the source workbook a
there is another step to be undertaken before I finish with the boo
for the day. If I perform these steps manually, only the destinatio
workbook closes and I can continue working with the source workbook bu
the macro closes both

Any suggestions please? My macro code follows:
Sheets("Daily").Copy
ActiveWorkbook.SaveAs FileName:="POL Commencements_" & Format(Now
"dd-mm-yyyy") _
& ".xls", FileFormat:=xlExcel9795, Password:=""
WriteResPassword:="", _
ReadOnlyRecommended:=True, CreateBackup:=False
ActiveWorkbook.Close

Any assistance would be greatly appreciated

 

[Dave Peterson]

I don't think I've ever seen excel do this.

What version of excel are you using?

(You sure it's not another portion of your code that's closing the original
workbook?)

 

[Ken Macksey]

Hi

You could try changing your code to something like this.

Sheets("Daily").Select
Sheets("Daily").Copy
Workbooks(2).SaveAs FileName:="POL Commencements_" & Format(Now,
"dd-mm-yyyy") _
& ".xls", FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=True, CreateBackup:=False
Workbooks(2).Close

HTH

Ken

 

[Lee Jeffery]

Dave,

I'm using Excel 97 on WinNT 4.0. I've also tried it on Excel 97 wit
WinXP. Same result. The code I copied into my original message is al
there is. It's attached to a command button. I tried Ken's suggestio
too but I'm still unable to keep the original file open.

Any thoughts?

Ken,
Thanks for your suggestion.

I tried using your amendment which sort of works. It keeps the new fil
open but it's the original file I would like to keep open and close th
copy. This is because I continue adding to the original after the cop
is made. The additional information is not meant to go in the copy a
it is not required by the end user of the copy.

I also tried changing the close command to Workbooks(1) to see if tha
made any difference but it was the same outcome.

Do you have any other suggestions I might try, please

 

[Ken Macksey]

Hi

I am not sure why you are having this problem. The code works fine for me in
Excel 97. Use a command button from the control toolbox and be sure to set
the TakeFocusOnClick property of the command button to false.


Private Sub CommandButton1_Click()

ThisWorkbook.Sheets("Daily").Select
ThisWorkbook.Sheets("Daily").Copy
Workbooks(2).SaveAs FileName:="POL Commencements_" & Format(Now,
"dd-mm-yyyy") _
& ".xls", FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=True, CreateBackup:=False
Workbooks(2).Close

End Sub


HTH

Ken

 

[Dave Peterson]

Maybe you could add some msgboxes to help you debug it (I couldn't get xl2002 to
misbehave).

Option Explicit
Sub testme01()

Dim newWkbk As Workbook

Sheets("Daily").Copy
Set newWkbk = ActiveWorkbook
MsgBox newWkbk.Name

newWkbk.SaveAs Filename:="POL Commencements_" & Format(Now, "dd-mm-yyyy") _
& ".xls", FileFormat:=xlExcel9795, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=True, CreateBackup:=False

MsgBox newWkbk.Name

newWkbk.Close

End Sub

Maybe it'll help you locate the problem.

 

[Lee Jeffery]

David & Ken,

Thanks guys. Don't really know what changes I have effected (I'
starting to dream code!). I have studied what I had and compared thi
to what is currently working and I cannot see why it didn't behav
before.

Dave's messagebox showed what appeared to be an additional workboo
opening and it was this third workbook which was remaining ope
although I couldn't see any code asking this to happen.

I recorded the macro again and all is working okay now.

Many thanks

 

[Dave Peterson]

Glad you got the magical beast working <vbg>.

David & Ken,

Thanks guys. Don't really know what changes I have effected (I'm
starting to dream code!). I have studied what I had and compared this
to what is currently working and I cannot see why it didn't behave
before.

Dave's messagebox showed what appeared to be an additional workbook
opening and it was this third workbook which was remaining open
although I couldn't see any code asking this to happen.

I recorded the macro again and all is working okay now.

Many thanks.