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

  • Thread starter Thread starter Lee Jeffery
  • Start date Start date
L

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
 
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?)
 
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
 
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
 
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
 
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.
 
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
 
Glad you got the magical beast working <vbg>.



Lee Jeffery < said:
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.
Click to expand...
 
Back
Top