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

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

--
Message posted from http://www.ExcelForum.com

0
6/27/2004 6:31:31 AM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
608 Views

Similar Articles

[PageSpeed] 50

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?)



"Lee Jeffery <" wrote:
> 
> 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 "POL
> Commencements (Today's date)".xls, and closes the new workbook. It is
> also closing the source workbook.
> 
> I would like to keep the source workbook open after the copying
> process. Alternatively, automatically reopen the source workbook as
> there is another step to be undertaken before I finish with the book
> for the day. If I perform these steps manually, only the destination
> workbook closes and I can continue working with the source workbook but
> 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.
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/27/2004 12:06:53 PM
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004


0
KenMacksey (98)
6/27/2004 2:33:39 PM
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

--
Message posted from http://www.ExcelForum.com

0
7/3/2004 1:18:50 PM
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004


0
KenMacksey (98)
7/3/2004 2:13:41 PM
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 <" wrote:
> 
> Dave,
> 
> I'm using Excel 97 on WinNT 4.0. I've also tried it on Excel 97 with
> WinXP. Same result. The code I copied into my original message is all
> there is. It's attached to a command button. I tried Ken's suggestion
> 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 file
> open but it's the original file I would like to keep open and close the
> copy. This is because I continue adding to the original after the copy
> is made. The additional information is not meant to go in the copy as
> it is not required by the end user of the copy.
> 
> I also tried changing the close command to Workbooks(1) to see if that
> made any difference but it was the same outcome.
> 
> Do you have any other suggestions I might try, please?
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/3/2004 5:31:28 PM
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

--
Message posted from http://www.ExcelForum.com

0
7/4/2004 5:45:54 AM
Glad you got the magical beast working <vbg>.



"Lee Jeffery <" wrote:
> 
> 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.
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/4/2004 11:38:59 AM
Reply:

Similar Artilces:

Excel 97 not saving
I have a user on WinXP / Office 97. User states that sporatically, she is unable to save an Excel file on either her hard drive or the network drive. These files have macros in them, and up until she was upgraded to XP and began using macros, she had no problems saving. The only error being reported is "Document could not be saved." In her latest email to me she writes: Please note that the problem is intermittent and I do not know what is triggering the problem other than it has only happened since I started using XP and files with Macros. I have seen documentation that a...

Outlook keeps prompting for logon..
User is on Outlook 2k3, latest service packs, updates, etc. Mailbox lives on Exchange 2007 (SP1). Everytime the user starts Outlook they're prompted to logon. I've disabled the logon prompt at startup option (and enabled it and then disabled it again), but it's still happening. I've tried it with cached mode on and cached mode off. I've tried it with offline mode enabled and disabled. I've tried deleting his profile and recreating it. I've tried switching to an RPC/HTTPS connection. Finally, i completely uninstalled Outlook 2k3 and installed Outlook 2k7. Not...

How do I save an entire outlook folder to my desktop?
I am trying to backup my whole folder to my desktop but can only do one message at a time. How do I save an entire outlook folder to my desktop? Thanks, Tom Do you want to copy the messages to your desktop or backup the entire pst file? http://www.slipstick.com/addins/housekeeping.htm#user http://www.slipstick.com/config/backup.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ Ou...

Opening Excel files in Explorer
Usually you can open any excel file by double clicking the file from within Windows Explorer. Since some time for a mysterious reason this doesn't work any more. Excel opens but not the file. Any ideas why this is so and what to do about it? Tanks for info, Danker -- Danker Sometimes one of these works: Tools|Options|General|Ignore other applications (uncheck it) --- or --- Close Excel and Windows Start Button|Run excel /unregserver then Windows Start Button|Run excel /regserver The /unregserver & /regserver stuff resets the windows registry to excel's factory defaults...

Unable to open sent items folder
I have delegated my freind to open my "sent Items" folder in my mail box ,but he is unable to open my "sent items" folder in his outlook2000. He is able to open my other folder like "inbox,calender" after delegation.Except the sent items. In oultook@ file >open other users folder>folder type ,the "sent items" folder is not listed .. Please help if there is any workaorund for this thanks SP He needs to add your mailbox to his folderlist. See http://www.howto-outlook.com/howto/permissions.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-out...

Error when trying to get Activities and History of Account, smells like Bug
I have two Accounts, that I get an error, every time I try to reach their activities and history! The error message comes like : An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. ActivityPointerWebService.RollupByObject [SoapException: Server was unable to process request.] System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) +1496 ...

how do i delete a (read only) file?
i am trying to delete some files that are (READ ONLY) anyone have a clue as to how? i am in microsoft excel. Open windows explorer. go to that folder right click on the file to delete select "delete" answer yes to the "are you sure prompt" (somethings don't need to be done in Excel.) Renee wrote: > > i am trying to delete some files that are (READ ONLY) anyone have a clue as > to how? i am in microsoft excel. -- Dave Peterson ...

Help Old addresses keep coming up
When I go to send an email to some people their old email addresses keep popping up together with their current ones. I do not have them listed in contacts or in an address book. It is a minor annoyance. I am using Outlook 2003. It is a minor annoyance. How do I delete these old addresses? Where they show up is where I am entering the e-mail address on the item being e-mailed. Thanks You did not specify how you were entering the addresses. Presumably you are using autocompletion and they are...

Excel File Opening Via Hyperlink
What do I need to do within an Excel document to have it open in print preview mode when the Intranet link to this file is clicked? Thank you. Put this one line Macro in the ThisWorkbook code area: Private Sub Workbook_Open() ActiveWindow.SelectedSheets.PrintPreview End Sub -- Gary''s Student - gsnu200723 ...

Confirmation of receipt keeps trying to send and fails
Help! I received an email message that requested a confirmation that it had been received. I told it to send confirmation, but it failed when attemtping to send it and produced an error. I tried deleting the email message and everything in the draft items and Outbox, but every time I switch on Outlook the darn thing tries to send this email confirmation. It seems there is nothing I can do to stop it, even though I cannot see it! I have tried uninstalling Office 2003 and reinstalling it, but all of my settings are remembered as are all the old emails. I just cannot seem to get rid of it! ...

Problems with opening attachments in Outlook 2000
Hello all, In Outlook 2000 we experience some strange problems. When we open an attachment with a wireless network card present we cannot open the attachment. If we remove the card or are connected to the normal network everything works just fine. We use Outlook with a pst file and several profiles. For as far as we can see this problem occurs in all profiles. If you have any idea... please let me know. Erwin ...

Making an exe file
I am creating a personal program using VB6 and Windows 7. I have not used VB6 for a couple of months and this time after the program has been completed and tested under the IDE I went to create an exe file but the Make Exe file and the Make Project Group items are grayed out. I found some references on the web that suggested starting a new program with just a single form and command button. I did this but the Make exe and project group menu items remained grayed out. I am running the vb6 program as Administrator. Any thoughts would be appreciated. Marv M Wade wrote: ...

copying cells #2
In cell A1 sheet1 I have a number which lets say represents a code for a specific shape that i have allready drawn in cell B1 in sheet2.Can you please tell me what kind of instruction i can give to cell B1,sheet1,so that i can get that shape from cell B1 sheet2 into B1 sheet1? How about putting those shapes on Sheet1 and hide them. Then you could have a macro unhide the one you want. J.E. McGimpsey shares this in a macro at his site: http://www.mcgimpsey.com/excel/lookuppics.html If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcrit...

Keep Lines Together; Keep with Next
I'm working in Vista. When I select lines and click on "Keep Lines Together," the lines do not stay together. They stay together if I also click "Keep with Next," but I'm working on a booklet in which the pages are 1/4 of a letter-sized page, and a lot of space (4 lines) is left at the bottom and wasted if I also click "Keep with Next." Why won't "Keep Lines Together" keep the lines together? The project is a small, bound country club booklet, with names, addresses, phones, etc. Each entry might be 2, 3, or 4 lines. Any h...

opening Excel and then closing it
I have a file that is passed to me in XLS format, but I would much prefer to work with it in CSV. So I did this... ' open the downloaded file in Excel and copy it over to L in csv format Workbooks.Open "O:\Downloads\D00100001554_0.xls" Workbooks("D00100001554_0.xls").ConflictResolution = xlLocalSessionChanges Workbooks("D00100001554_0.xls").SaveAs "L:\Activity.csv", xlCSV Workbooks("Activity.csv").Close False This works fine except it leaves Excel open in the background. The only thing to do is fine it in Processes a...

why do my pictures keep disappearing?
Although I have checked the box in 'pictures' any form of graphic or word art that I have inserted into my publication keeps disappearing! If I click in the general direction of the insertion the bounding dots appear, but no picture Are you sure you have view, pictures, detailed display checked? If so try updating your graphics driver... -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "JG" <JG@discussions.microsoft.com> wrote in message news:57E101AC-A758-400A-9269-3B68B754CA0E@microsoft.com... > Altho...

error: File may have been changed
ok this is my first time to this forum or any forum that dusnt have to do with my car so please bear with me...i am having an issue at work with saving files in excel. the error reads as follows: The file '...' may have been changed by another user since you last saved it. In that case what do you want Save A Copy Overwrite Changes All the files i am saving are on a network folder which no one has access to except myself. This means that no one can be saving my files. If i hit ssave in excel it brings up this box and i will hit overwrite changes than if i hit save it asks me again....

how can I keep a tab in place?
I have workbooks with multiple worksheets. I would like to keep the summary tab in a fixed (first) position so that the user can easily, quickly find it without using the l<, <, >, >l (arrow) tabs. I use hyperlinks to get from the summary tab to the other tabs, and I know I can hyperlink tab back... but I'd rather not use the hyperlinks too much. Tools - Protection - Protect workbook (for structure) Or, create a custom button/macro that navigates to the summary sheet. -- Best Regards, Luke M "missy8892" <missy8892@discussions.microsoft.com>...

Money
This has been going on for many days. Please address this problem. "Greg K" <Greg K@discussions.microsoft.com> wrote in message news:20ED6E8B-08EB-4E86-9C6A-72562CF95BEF@microsoft.com... > This has been going on for many days. Please address this problem. If you think posting something here will have an affect, you're wrong. MS or rather MS Supplier is working on it. The NAV is actually changing but the date isn't. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do no...

Moving my files to a new computer
So I've copied my old .pst file to my new computer, used Tools>options>mail setup>data files to introduce outlook to it, and reset it to to default and removed the old entry so there is just the one entry in the list. Both my new and old outlooks are 2003. So far so good and I can see my mail folders and contacts in the left hand pane, *but* tools>address book is not showing any of my contact addresses, nor will check names show them. Anyone know whay or can point me at an article that tells me what to do? Also, I have several email accounts is there an easy way of movi...

Cannot open project file
Hi Can anybody suggest a reason why my Project 2003 file has suddenly stopped opening? It displays the error message 'Project cannot recognise this file format. Do you want to open the file as text only'. Anybody come across this before? Thanks Hi, Is this all files or just this one? If it's just the one, file corruption is a possibility, try a backup copy. If all files, then have you just updated to SP3? If so, legacy (older formats) are restricted by default. Under Tools, Options, Security tab, select the third option, allow legacy formats and try agai...

Activity Report 11-15-04
I have a client that needs to be able to run daily activity reports for sales reps. This report would show all completed activities for a particular day. Does anyone have any ideas on how to accomplish this without writing any code? Thanks. This is possible using the standard filters on the Activity List Report. You may not have noticed, but there is a small "chevron" icon in the upper right hand corner which exposes a set of filters which include the status and due date fields, which I believe to be sufficient to produce the report you're looking for. Hope that hel...

Exchange 2003 send as permission,please help!
Hi All I have Exchange 2003 standard. Put simply I have followed the exact ms knowledge base item on how to allow "send as " permission to another mailbox. I have selected the mailbox in question,selected avanced,security,and added in a user who i want to give the send as permission to. Then selected give send as permission to the user and got them to log off and back on again. It doesn't work and you get "you do not have permission to send on behalf of the specified user". I gave it a day in case it had to read its configeration but still no joy. Can you hel...

Save As option
I'm working in Word 2003. When I open some files and try to save as a different name, the "SAve As" option is not available available. Only the Save option. How do I make save as appear under File ? Try this: Right click on the "file...edit...." toolbar| Click Customize at bottom | Go to tab "Commands" | scroll down Commands and find "Save As" | Drag the button up to File and put it where you want. "KP" wrote: > I'm working in Word 2003. When I open some files and try to save as a > different name, the "...

PST file
I am using Outlook 2000 - corporate / workgroup configuration. I have a large PST file that I use to archive old emails. The file is 2GB! I got an error message, and cannot access or open the file. Error message is. "UNABLE TO DISPLAY THE FOLDER. ERRORS HAVE BEEN DETECTED in xxx.PST FILE. QUIT & USE INBOX REPAIR TOOL". I tried the inbox repair tool, but again, an error occured before it finised "phase 4 of 8". I think the error is due to the file being so large..... I just want to be able to access it to backup some of the data to disk, and trim down so...