Need a macro to work on any filename

[wcthrill]

I have several macros in my Personal Wrkbook. They all do different tasks but look for the file named aging.xls
I run different reports and didnt want to give each one a different file name, which is why I name any file I export from our accounting system 'aging'

Although this technique works fine Id like to create a macro to run on the current file I have open regardless of the file name.

Is this possible?

 

[gruntingmonkey]

how about naming the workbook when its opened so you can refer to it by a specific name

wb = ActiveWorkbook.Name

 

[wcthrill]

how about naming the workbook when its opened so you can refer to it by a specific name

wb = ActiveWorkbook.Name

Not sure I understand. I dont work much in VB.

Below is sample code on one of my macros

Sub AGING_FORMAT()
'
' AGING_FORMAT Macro
'

'
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("D:G").Select
Selection.Delete Shift:=xlToLeft
Columns("A:L").Select
Columns("A:L").EntireColumn.AutoFit
Range("B2").Select
Columns("B:B").ColumnWidth = 26.22
Range("C1").Select
ActiveCell.FormulaR1C1 = "Parent"
Range("C2").Select
Columns("C:C").ColumnWidth = 4.33
Columns("C:C").ColumnWidth = 4.44
Columns("C:C").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 17.11
Workbooks.Open Filename:= _
"X:\Finance_Dept\Accts_Receivable\Aging Reports\Accounts By Collector.xlsx"
Windows("aging.xls").Activate
Selection.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[Accounts By Collector.xlsx]aging'!R2C1:R5301C5,5,0)"
Selection.Copy
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveWindow.SmallScroll Down:=-12
Application.CutCopyMode = False
Selection.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[Accounts By Collector.xlsx]aging'!R2C1:R5301C3,3,0)"
Selection.Copy
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
Range("L1").Select
ActiveCell.FormulaR1C1 = "Collector"
Range("L2").Select
Selection.FormulaR1C1 = _
"=VLOOKUP(RC[-11],'[Accounts By Collector.xlsx]aging'!R2C1:R5301C5,5,0)"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
Selection.Copy
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 11).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("F1").Select
ActiveCell.FormulaR1C1 = "Current"
Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("L1").Select
ActiveCell.FormulaR1C1 = "Over 60"
Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("L1").Select
ActiveCell.FormulaR1C1 = "Over 30"
Range("L2").Select
Selection.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Selection.Style = "Comma"
Selection.Copy
Range("N2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.Select
ActiveWindow.SmallScroll Down:=-9
Range("N2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("L2").Select
Application.CutCopyMode = False
Selection.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Selection.Style = "Comma"
Selection.Copy
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=-6
Range("N2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("M2").Select
Application.CutCopyMode = False
Selection.FormulaR1C1 = "=SUM(RC[-4]:RC[-2])"
Selection.Style = "Comma"
Selection.Copy
Range("N2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("N2").Select
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Selection.AutoFilter
End Sub

 

[gruntingmonkey]

You've been a member of this forum for longer than I have so I'm going to moan about how you should actually learn from this site rather than relying on someone else to do it for you....

I will however help you out.

Sub AGING_FORMAT()
 
 ' AGING_FORMAT Macro
 
 Columns("A:A").Selection.Delete Shift:=xlToLeft
 Columns("D:G").Selection.Delete Shift:=xlToLeft
 Columns("A:L").Columns("A:L").EntireColumn.AutoFit
 Columns("B:B").ColumnWidth = 26.22
 Range("C1").value = "Parent"
 Columns("C:C").EntireColumn.AutoFit
 
 Workbooks.Open Filename:= _
 "X:\Finance_Dept\Accts_Receivable\Aging Reports\Accounts By Collector.xlsx"
 
 wb = ActiveWorkbook.Name
 
 Windows("aging.xls").Activate 			'I am assuming this is the name of the file with the Macro in it
 
 Selection.FormulaR1C1 = _
 "=VLOOKUP(RC[-2],'[wb]aging'!R2C1:R5301C5,5,0)"
 Selection.Copy
 Range("B2").Select
 Selection.End(xlDown).Select
 ActiveCell.Offset(0, 1).Range("A1").Select
 Range(Selection, Selection.End(xlUp)).Select
 ActiveWindow.SmallScroll Down:=-12
 Application.CutCopyMode = False
 Selection.FormulaR1C1 = _
 "=VLOOKUP(RC[-2],'[wb]aging'!R2C1:R5301C3,3,0)"

You can see from the above that I have renamed the open workbook to WB so you can refer to it as wb from then on.

Other things to note, If you record Macros then it records your mistakes too - I can see you have probably not even looked at this code before pasting.

 

[wcthrill]

Not sure what the relevance on how long Ive been a member has to do with finding my own answers. Prior to asking a Q on any forum I research first.
When I cant find a solution I use the forum as a last resort.

Although I appreciate your work on the macro..its not what I was requesting. It will only work if the file name is 'aging' which is what I wanted to avoid.
The macro I have works perfectly..I know there are some added code , but VB is NOT my forte...

That being sd I needed a macro to run on any open currently open file regardless of the file name.

Ill simply continue to use my macro and save everyone time and 'moaning'

 

[gruntingmonkey]

I didn't mean to come across like that so apologies. The following is the principle that you need to follow:

You need to rename the workbook when it opens with reference instead of the actual file name so you know what your referencing.

Sub Macro1()

Workbooks.Add                              'this is in place of whatever file you open - opens a workbook

 wb = ActiveWorkbook.Name          'renames in the code only the file above as "wb"

 Windows(wb).Activate                   'activates the workbook wb
    
'put your code here
End Sub

or you could also use

'or
ActiveWindow.ActivateNext              'This activates the next window 
'You need to be careful here and make sure that only the files you require are open

wb = ActiveWorkbook.Name            'renames in the code only the file above as "wb"

if wb <> thisworkbook.name then    'if it isn't this workbook
Windows(wb).Activate                     'activates the workbook wb

'put your code here

else
'do nothing
end if

I hope that makes more sense then my previous answer.

 

[wcthrill]

Thanks