macro are evil!! why wont they work..

[Stormrages]

hay,

ok kinda interesting problem... i work with a marco program which allows me to generate an invoice per-customer, it did at one stage work on all of 4 of my computers.. Now it only works on one.. (as it works on the one i know it is not the actual program).. but when i try to get it to run on the others it comes back with a run-time error '9' Subscript out of range, but it is giving the name of the correct workbook and the workbook is open, its just like it cant see it.. ,(its not a network problem as each computer has its own macro), i total wiped one computer last weekend and re-installed windows 2003 but that did not help...can anyone think of anything that would stop a macro working if the actual macro its self worked..

 

[Norie]

When you reference the workbook in code are you using the .xls file extension?

 

[Stormrages]

here is the code i use, its done in a template form and should in theory copy the template but it is getting stuck on the Workbooks(NewWorkbookName).Activate line, which is just weird as it can do it on one computer and then when i open the same files (saved on the desktop) it does not.. (i'm pulling my hair out, cause it used to work!!!, how can it suddenly stop working!!!)

Sub NewInvoice()
'
' NewInvoice Macro
' Macro recorded 22/12/2002 by Joan
'

'

Dim CustomerName
Dim AuctionDate
Dim NewSheetName
Dim NewWorkbookName

Dim NewInvoice As Worksheet
Dim NewWorkbook As Workbook



' ActiveSheet.Cells(7, 3).Activate
' Selection.Copy


' Get Customer Name and AUCTION DATE
Sheets("Control").Select
Set CustomerName = Cells(7, 3)
Set AuctionDate = Cells(3, 3)

' Get template for invoice

Sheets("InvoiceTemplate").Select
Cells.Select
Selection.Copy

' Sheets("Sheet4").Select
' Sheets("Sheet4").Name = "cdnslocndsco"

' Activate Invoice workbook
NewWorkbookName = "Auction_" & AuctionDate & "_Invoices"
Workbooks(NewWorkbookName).Activate


' Add new sheet
Sheets.Add
Set NewInvoice = ActiveSheet
' MsgBox (CustomerName)
' Sheets(NewInvoice.Name).Select


' NewSheetName = AuctionDate & "_" & CustomerName
NewSheetName = CustomerName

Sheets(NewInvoice.Name).Name = NewSheetName
ActiveSheet.Paste


Cells(23, 3) = CustomerName

Workbooks("Auction_Excel").Activate
Sheets("InvoiceTemplate").Activate
Set Refnumber = Cells(7, 2)
Cells(7, 2).Select
Selection.ClearContents
Finish:
Application.EnableEvents = True
Application.ScreenUpdating = True

' Activate Unsold Doc Workbook
NewWorkbookName = "Auction_" & AuctionDate & "_Invoices"
Workbooks(NewWorkbookName).Activate

End Sub

 

[Norie]

Have you tried adding the .xls extension?

If a user has set there Windows environment to show file extensions then that usually solves the problem.

Sub NewInvoice()
Dim CustomerName
Dim AuctionDate
Dim NewSheetName
Dim NewWorkbookName
Dim NewInvoice As Worksheet
Dim NewWorkbook As Workbook

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Sheets("Control")
        CustomerName = .Cells(7, 3)
        AuctionDate = .Cells(3, 3)
    End With
    ' Get template for invoice
    
    Sheets("InvoiceTemplate").Cells.Copy
    
    NewWorkbookName = "Auction_" & AuctionDate & "_Invoices.xls"
    
    Set NewInvoice = Workbooks(NewWorkbookName).Sheets.Add
    
    NewInvoice.Name = CustomerName
    
    NewInvoice.Paste
    
    NewInvoice.Cells(23, 3) = CustomerName
    
    Workbooks("Auction_Excel.xls").Sheets("InvoiceTemplate").Cells(7, 2).ClearContents
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    ' Activate Unsold Doc Workbook
    NewWorkbookName = "Auction_" & AuctionDate & "_Invoices"
    
    Workbooks(NewWorkbookName).Activate
    
End Sub

 

[Stormrages]

Hay it does seem to be working.. fair play to you Norie!!! that is deadly... thank you so much ....