PDA

View Full Version : Issue in activating the workbook with partial name



VPPANEER
10-12-2016, 12:16 PM
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

SamT
10-13-2016, 06:43 AM
Try removing the space

Workbooks("*Dump Aging Report*_" & ".xls").Activate

VPPANEER
10-13-2016, 10:13 AM
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

SamT
10-13-2016, 12:38 PM
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?

VPPANEER
10-14-2016, 12:50 PM
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"

SamT
10-14-2016, 03:56 PM
Here i am looking for a Reference : IC1012.


ActiveSheet.Range("$A$1:$Y$1299").AutoFilter Field:=17, Criteria1:="IC1012"

???