Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA code in a macro (Excel 2000)

    This macro worked all week long. Today when I run it the macro stops on line 3 at the "Set". Can you help me?

    ' Sub Interbranch()
    '
    ' InterbranchCopy Macro
    ' Macro recorded 11/09/2004 by bxwallac
    '
    Dim DWB As Workbook
    Dim DestinationBook As String
    Set CWB = ActiveWorkbook
    DestinationBook = Application.GetOpenFilename("All Excel Files,*.xls")
    Workbooks.Open DestinationBook
    Set DWB = ActiveWorkbook
    Sheets("Inter-Branch Allocation").Activate
    ActiveSheet.Unprotect
    Sheets("Detailed Financials").Activate
    ActiveSheet.Unprotect
    Sheets("Monthly Plan Summary").Activate
    ActiveSheet.Unprotect
    CWB.Activate
    Sheets("Inter-Branch Allocation").Range("A1216:BI1251").Copy DWB.Sheets("Inter-Branch Allocation").Range("A1216")
    Sheets("Detailed Financials").Range("r82:BT82").Copy DWB.Sheets("Detailed Financials").Range("r82")
    Sheets("Detailed Financials").Range("r95:BT95").Copy DWB.Sheets("Detailed Financials").Range("r95")
    Sheets("Monthly Plan Summary").Range("p29:BR29").Copy DWB.Sheets("Monthly Plan Summary").Range("p29")
    DWB.Activate
    ActiveWorkbook.ChangeLink Name:="Interbranch 11-16-2004 Cindy.xls", NewName:=DWB.FullName, Type:=xlExcelLinks


    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA code in a macro (Excel 2000)

    Do you have option Explicit on?

    The variable CWB has not been DIMmed.

    If not the issue, what is the error?
    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA code in a macro (Excel 2000)

    Do you have a workbook open? If your macro is in the Personal Macro Workbook, there is a reference to the macro even if there is no workbook open. However, without a workbook open, the code will debug on line 3 as there is no workbook available to save to the CWB variable!
    If you want to run the macro without a workbook being open, change the code to "Set CWB = Workbooks.Add". I will also modify the macro to include the DIM statement as Steve has wisely mentioned. Type Dim CWB as Workbook above line 3.
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •