Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!nuzba.szn.dk!pnx.dk!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail From: R C Nesbit Newsgroups: comp.lang.basic.visual.misc Subject: Re: VB6 excel automation breaking Date: Mon, 04 Mar 2013 11:49:32 -0000 Organization: SpamTrap Lines: 38 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Trace: individual.net 4cdtQRvwrDJhNRufXt/xJwp5BnQ5mEmDmycjG9YcwByYwsdUG9lTYpsgF/e3RyJje0 Cancel-Lock: sha1:bjxeklcKE8QHvQAVYX8UfriO0gk= X-Newsreader: Virtual Access Open Source http://www.virtual-access.org/ Xref: csiph.com comp.lang.basic.visual.misc:1732 Gs spoke: > Stuart's suggestion should work for your stated problem, I see another > problem you might want to change your approach on. > > > oWB.Sheets("Name (2)").Name = "Name" > > This line is trying to rename the copied sheet to the same name as the > sheet it was a copy of. I'd expect you'd be renaming it to the one of > the names in your rsT, and keep the sheet "Name" as the template... > > 'Add a copy of sheet "Name" for each name in rsT > oWB.Sheets("Name").Copy After:=oWB.Sheets(i) > 'The copy is now the active sheet > With oWB.ActiveSheet > .Name = rsT.Fields(0) '//rename immediately > .Cells(1, 2) = rsT.Fields(0): .Cells(1, 7) = sReportDate > End With > rsT.MoveNext > Loop Well thanks to both of you, the issue is resolved. The reason for renaming the original template sheet to rsT.Fields(0) and the new copy to Name was that the blank template was automatically moved along and ended up as the last worksheet. Renaming the copy leaves the blank template sheet just after the sheet(1) summary. fixed by adding: oWB.Sheets("Name").Move After:=oWB.Sheets(sName) oWB.Sheets("Name").Delete doesn't work very well as Excel pops up a dialog for confirmation. -- Rob Pearson