+ Reply to Thread
Results 1 to 3 of 3

multiple jump error

  1. #1
    Registered User
    Join Date
    03-15-2013
    Location
    CA, USA.
    MS-Off Ver
    Excel 2007
    Posts
    2

    multiple jump error

    Let me start by saying that I'm fairly new to making macros and that I'm teaching myself. Any help would be greatly appriciated.

    I created this macro for some reports I run at work. What it should do is open a file, format it, copy a cell, paste that into a new sheet then save it under a new name. That all works, the issue is in the jump commands. They way I'd like it to work is; if there is not a particular file it skips that section and moves to the next. That works but only if there is one jump, if multiple jumps are needed an error occurs. Any tips would be helpful. there are 4 possible jumps total. I've put part of the code below;
    'Dole
    'open original file
    On Error GoTo Eaches
    Workbooks.Open Filename:="G:\Banana-ddd-Dole.xls"
    Windows("Banana-ddd-Dole.xls").Activate

    'format file
    (removed to save space)

    'copy function
    Range("I5").Select
    Selection.Copy

    'open copy location
    Windows("MACRO - banana report.xls").Activate
    Sheets("E-mail report").Select

    'need paste function
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Windows("Banana-ddd-Dole.xls").Activate


    'save file w/ julian date
    'Format(DateDiff("d", "1/1" & "/" & Year(Date), Date) + 1, "000")
    ActiveWorkbook.SaveAs Filename:="G:\Banana-" & Format(DateDiff("d", "1/1" & "/" & Year(Date), Date) + 1, "000") & "-Dole" & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close

    'Eaches
    Eaches:

    'open original file
    On Error GoTo Mini
    Workbooks.Open Filename:="G:\Banana-ddd-Eaches.xls"
    Windows("Banana-ddd-Eaches.xls").Activate

    'format file
    (removed to save space)

    'copy function
    Range("I5").Select
    Selection.Copy

    'open copy location
    Windows("MACRO - banana report.xls").Activate
    Sheets("E-mail report").Select

    'need paste function
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Windows("Banana-ddd-Eaches.xls").Activate


    'save file w/ julian date
    'Format(DateDiff("d", "1/1" & "/" & Year(Date), Date) + 1, "000")
    ActiveWorkbook.SaveAs Filename:="G:\Banana-" & Format(DateDiff("d", "1/1" & "/" & Year(Date), Date) + 1, "000") & "-Eaches" & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    ActiveWindow.Close

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: multiple jump error

    Hi

    when opening files I usually adopt the folowing process:-

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-15-2013
    Location
    CA, USA.
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: multiple jump error

    Thank you for the tip, I tried that but I'm still getting the same issue.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1