Consulting

Results 1 to 6 of 6

Thread: Issue in activating the workbook with partial name

  1. #1
    VBAX Newbie
    Joined
    Oct 2016
    Posts
    3
    Location

    Issue in activating the workbook with partial name

    Hi There,

    Below is my code, which i used to activate a workbook with partial file name but it it is showing error.

    Kindly help to get this issue resolved.

    Sub DRTN()
        ' DRTN Macro
        Cells.Select
        Range("Q1").Activate
        Selection.AutoFilter
        Selection.AutoFilter
        Range("Q1").Select
        ActiveSheet.Range("$A$1:$Y$1299").AutoFilter Field:=17, Criteria1:="IC1012"
        Range("P1").Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        ActiveSheet.Previous.Select
        ActiveSheet.Paste
        Cells.Select
        Selection.Columns.AutoFit
        Columns("A:B").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlToLeft
        Columns("B:B").Select
        Selection.Delete Shift:=xlToLeft
        Columns("C:H").Select
        Selection.Delete Shift:=xlToLeft
        Selection.End(xlUp).Select
        Columns("I:I").Select
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.End(xlUp).Select
        Selection.End(xlToRight).Select
        Columns("L:L").Select
        Selection.Delete Shift:=xlToLeft
        Range("I1").Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "Invoice #"
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Invoice Date"
        Range("E1").Select
        ActiveCell.FormulaR1C1 = "Amount"
        Range("F1").Select
        ActiveCell.FormulaR1C1 = "Currency"
        Range("H1").Select
        ActiveCell.FormulaR1C1 = "Vendor #"
        Range("K1").Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Columns.AutoFit
        Columns("G:G").Select
        Selection.Delete Shift:=xlToLeft
        Selection.Delete Shift:=xlToLeft
        Selection.End(xlUp).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Range("A1").Select
        Workbooks("*Dump Aging Report* " & ".xls").Activate
    End Sub
    Last edited by Aussiebear; 12-13-2024 at 10:58 PM.

  2. #2
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    Try removing the space

    Workbooks("*Dump Aging Report*_" & ".xls").Activate
    Last edited by Aussiebear; 12-13-2024 at 10:59 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Newbie
    Joined
    Oct 2016
    Posts
    3
    Location
    Again it is showing an error, i have a file name as Dump Aging Report DD.MM.YYYY which changes daily as per the date and the below code is not working. Pls Assist

    Workbooks("*Dump Aging Report*" & ".xls").Activate
    Last edited by Aussiebear; 12-13-2024 at 10:59 PM.

  4. #4
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    Note character (Space) after 'Report.' Edit to suit.

    Todays:
    Workbooks("Dump Aging Report " & Format(Date, "dd.mm.yyyy") & ".xls").Activate
    Yesterdays:
    Workbooks("Dump Aging Report " & Format(Date - 1, "dd.mm.yyyy") & ".xls").Activate
    Also try Windows( etc etc etc ).Activate

    Is that workbook open when you run the code?
    Last edited by Aussiebear; 12-13-2024 at 10:59 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Newbie
    Joined
    Oct 2016
    Posts
    3
    Location
    To make this code to work, i changed the file name to Dump Aging Report and now its working fine...

    Also how to set up a Message box, if the result is False in the below code. Here i am looking for a Reference : IC1012.

    ActiveSheet.Range("$A$1:$Y$1299").AutoFilter Field:=17, Criteria1:="IC1012"
    Last edited by Aussiebear; 12-13-2024 at 11:00 PM.

  6. #6
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    Here i am looking for a Reference : IC1012.
    ActiveSheet.Range("$A$1:$Y$1299").AutoFilter Field:=17, Criteria1:="IC1012"
    ???
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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