macro are evil!! why wont they work..

Stormrages

New Member
Joined
Feb 14, 2005
Messages
47
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..
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,234
Office Version
  1. 365
Platform
  1. Windows
When you reference the workbook in code are you using the .xls file extension?
 

Stormrages

New Member
Joined
Feb 14, 2005
Messages
47
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,234
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Feb 14, 2005
Messages
47
Hay it does seem to be working.. fair play to you Norie!!! that is deadly... thank you so much ....
 

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,587
Members
414,460
Latest member
uctc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top