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

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

    Workbooks("*Dump Aging Report*_" & ".xls").Activate
    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

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    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?
    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"

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    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
  •