1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro Won't Open in Different Workbook

Discussion in 'Business Applications' started by BettyAtKitchen, Sep 27, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. BettyAtKitchen

    BettyAtKitchen Thread Starter

    Joined:
    Apr 22, 2008
    Messages:
    116
    Using Excel 2010. I regularly download data from a database which have to be sorted by

    column 1 - cell color blue on top, then
    column 1 - cell color orange on top, then
    column 4 - value, from newest to oldest

    the data is always downloaded automatically to a workbook named "ABC", and the worksheet is always automatically named ABC#, with the # changing automatically.

    the macro is saved in the personal folder. I tried to record using absolute and then as relative reference. the macro NEVER works when I tried to run in any new downloaded workbook or even I recreated the same data in a new untitled workbook.

    the number of columns is always the same but the number of rows varies. there are about (7) cell colors, but only the blue and the orange need to be on top

    the recorded macro always indicate the name of the worksheet and workbook on which it was created. is this why it does not work on any other workbook or worksheet?

    any help will be appreciated
     
  2. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    I think you need to post the code. It sounds to me like the original macro has specific references to the file you originally used. For example in your original macro, how does it refer to the workbook and sheet name you need sorting / processing?
     
  3. BettyAtKitchen

    BettyAtKitchen Thread Starter

    Joined:
    Apr 22, 2008
    Messages:
    116
    A file is downloaded from a database. To open the file, this message appears:

    The file you are trying to open, ABC(88).xls’ is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

    The filename ABC(88) is automatically generated, with the letters ABC remaining constant. The number between the parenthesis increases after each download. Normally I would try and record a macro from this opened file. It never worked.

    So I tried to save the file and noticed the file type automatically indicates Web Page type. I do not know if this is significant.

    The new file is named as SampleExcel with the file type Excel.xlsx.

    Using relative reference, macro is recorded with the name MacroSampleExcel in Personal Macro Workbook.

    Ctrl+A twice to select entire worksheet, not just the spreadsheet. Recorded the macro.

    I download another set of records and tried to run the macro directly on the downloaded file, meaning that it was not saved to an xlsx format.
    When the macro is run, message Run-time error ‘9’: subscript out of range appears. When I click on debug, this window appears

    Sub MacroSampleExcel()
    '
    ' MacroSampleExcel Macro
    '

    '
    ActiveCell.Cells.Select
    Activeworkbook.Worksheets("ABC (88)").Sort.SortFields.Clear
    Activeworkbook.Worksheets("ABC (88)").Sort.SortFields.Add(ActiveCell.Range( _
    "A1:A1243"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
    = RGB(0, 0, 255)
    Activeworkbook.Worksheets("ABC (88)").Sort.SortFields.Add(ActiveCell.Range( _
    "A1:A1243"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
    = RGB(255, 165, 0)
    Activeworkbook.Worksheets("ABC (88)").Sort.SortFields.Add Key:=ActiveCell. _
    Offset(0, 3).Range("A1:A1243"), SortOn:=xlSortOnValues, Order:=xlDescending _
    , DataOption:=xlSortNormal
    With Activeworkbook.Worksheets("ABC (88)").Sort
    .SetRange ActiveCell.Offset(-1, 0).Range("A1:p1244")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub


    Downloaded another set of records, saved the file as .xlsx, macro won’t run. This time

    '
    ' MacroSampleExcel Macro
    '

    '
    ActiveCell.Cells.Select
    Activeworkbook.Worksheets("ABC (88)").Sort.SortFields.Clear
    Activeworkbook.Worksheets("ABC (88)").Sort.SortFields.Add(ActiveCell.Range( _
    "A1:A1243"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
    = RGB(0, 0, 255)
    Activeworkbook.Worksheets("ABC (88)").Sort.SortFields.Add(ActiveCell.Range( _
    "A1:A1243"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
    = RGB(255, 165, 0)
    Activeworkbook.Worksheets("SAE (88)").Sort.SortFields.Add Key:=ActiveCell. _
    Offset(0, 3).Range("A1:A1243"), SortOn:=xlSortOnValues, Order:=xlDescending _
    , DataOption:=xlSortNormal
    With Activeworkbook.Worksheets("ABC (88)").Sort
    .SetRange ActiveCell.Offset(-1, 0).Range("A1:p1244")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub





    Note: The macro should run to include all rows that have a record. Sometimes a download would have less, sometimes more rows. The number of columns remains constant

    Thank you.
     
  4. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    The second macro has a different sheet reference in it:


    ("SAE (88)")


    Might that be the source of the problem?
     
  5. BettyAtKitchen

    BettyAtKitchen Thread Starter

    Joined:
    Apr 22, 2008
    Messages:
    116
    it might be.. but i do not know how to fix it. remember, the sheets are automatically named when downloaded. how or what should i change in the macro?

    thanks
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You can set a variable equal to the sheet's name and then reference by the variable name instead or another option would be to reference the sheet's index number instead of the actual name

    How many total sheets are in the workbook that is downloaded and does the sheet that need to be processed always appear in the same position such as the the first sheet or last sheet in the active workbook?


    Rollin
     
  7. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Here you go ... I hope I've understood you right, this should work based on the file you are working with having one sheet that is named as you describe ABC(88)


    If the prefix is different you can edit the code in red below


    Sub BaK()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "ABC(*" Then
    Exit For
    End If
    Next

    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add(ActiveCell.Range( _
    "A1:A1243"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
    = RGB(0, 0, 255)
    ws.Sort.SortFields.Add(ActiveCell.Range( _
    "A1:A1243"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
    = RGB(255, 165, 0)
    ws.Sort.SortFields.Add Key:=ActiveCell. _
    Offset(0, 3).Range("A1:A1243"), SortOn:=xlSortOnValues, Order:=xlDescending _
    , DataOption:=xlSortNormal
    With ws.Sort
    .SetRange ActiveCell.Offset(-1, 0).Range("A1:p1244")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub




    ___


    If you've lots of sheets named in a similar way and you want them all to be processed, we'll have to edit the above code slightly.


    Your problem with the file not opening is likely caused by right clicking on a filename on a website, webmail and saving just the link (which will be html / web type) rather than saving the file itself.
     
  8. BettyAtKitchen

    BettyAtKitchen Thread Starter

    Joined:
    Apr 22, 2008
    Messages:
    116
    I copy/pasted Lex's macro from the line

    Dim ws As Worksheet to the last line. the macro did not work and when i went to the vba window.. the line

    ws.Sort.SortFields.Clear

    was highlighted in yellow.

    the new downloaded file has more than 2,000 rows... but if my understanding is correct, lex's macro will work only up to 1,243 rows???


    rollin: the downloaded file is always on one sheet... the columns names are always the same, and always have the same number of columns.

    thanks.
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    The number of rows coded will be the limit.

    So if 1243 is the last row, forgte about row 1244 and further.

    You;ll have to code for the number of rows.

    To detect the last row in a column A:

    Range("A" & Rows.Count).End(xlUp).Row will return the last filled row in column A



    Lets say column
     
  10. BettyAtKitchen

    BettyAtKitchen Thread Starter

    Joined:
    Apr 22, 2008
    Messages:
    116
    Hans...

    the macro does not work.. before or after your "Range("A" & Rows.Count).End(xlUp)" was added.

    "ws.Sort.SortFields.Clear" is highlighted in yellow.

    thanks
     
  11. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    OK.


    Add this macro to your system:
    Sub SheetNameCheck()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "ABC (*" Then 'note the added space in this code after ABC
    Exit For
    End If
    Next
    On Error GoTo NoSuchSheet
    MsgBox ws.Name
    Exit Sub


    NoSuchSheet:
    MsgBox "No Matching Sheet Found"
    End Sub


    I wonder if you are having a problem because your sheet isn't named as per the macro. If you run the above macro, it should pop up a message box with the correct sheet name ... else it will say "No Matching Sheet Found"


    EDIT

    Actually I think I can see the problem ... your sheets seem to have a space between the ABC and ( ... so you just need to edit the red portion of the code on your system to add that space (which, unfortunately I cannot correct above - post 30/09/14)
     
  12. BettyAtKitchen

    BettyAtKitchen Thread Starter

    Joined:
    Apr 22, 2008
    Messages:
    116
    Code.. I do not think the ABC+space is an issue because in the original macro recorded, there is no space. Macro still not working.
     
  13. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1134396

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice