[dba-Tech] Excel VBA problem

Is this :-

Sheets(i).Copy _
                     Before:=Workbooks(newFN).Sheets(1)   

Saying:-


Copy from sheet (i) to new work sheet and position before .Sheets(1)?

Can you do that?  Is there anything before sheet(1)
Or have I misunderstood

Max



-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Stephen
Sent: 25 May 2009 12:44
To: dba-tech at databaseadvisors.com
Subject: [dba-Tech] Excel VBA problem

This is bugging me.   I've Googled and tried the MS
solutions, and a couple of others, to no avail.  I even
recorded a Macro, but of course the variables I need are
constants, and changing those hasn't helped.
 
I have programmatically built several sheets in the
processing workbook ... to finish off I want to send these
sheets out to another WorkBook.  Then I <close no save> the
processing workbook.
 
 
'==============='  now send out to separate Workbook for
archive
Dim mySheetCount As Long, newFN As String
  Call InstantiateExcel
 ' create a new WorkBook 
  newFN = CreateNewExcelWorkbook(ActiveSheet.Name) & ".XLS"
  xlApp.Visible = True
  Call DestroyThisInstanceOfExcel    ' this all works to
here.  The new file is created 
                                     ' and the extra
instance of Excel has gone
  
  ThisWorkbook.Activate         ' probably redundant
  mySheetCount = ThisWorkbook.Sheets.Count      '
mySheetCount is 12, could be anything
  For i = 1 To mySheetCount
    Debug.Print "This:" & ThisWorkbook.Sheets(i).Name    '
this works here, 1st time thru 
                                                         '
and gives the name of the first sheet as expected
    ThisWorkbook.Sheets(i).Activate          ' probably
redundant
    Workbooks(WorkbookName).Sheets(i).Copy _
                     Before:=Workbooks(newFN).Sheets(1)   '
this is the line it fails on
  Next i
  
=================
 
The error message is Error 9 Subscript out of range, which
I've learned over the years could mean anything.
 
The data values just prior to the failing line are:
  WorkbookName = ProcessingForConslidation.xls  (then name
of the workbook this code is embedded in)
  newFN =
C:\WorkingOnXP\BRDConsolidated\ConsolApr09_RunOn09May2520445
9.xls  
                          (this is the new file created at
the top of the code above)
 
  i = 1  (it fails on the first pass thru the loop)
 
 
Please point out what I am sure is an obvious error ....... 
 
 
Stephen Bond  Ô¿Ô¬ 
 
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com