File name

[poikl]

Hi, can you please provide VBA code that will insert the excel FILE NAME all the way down in ColH?

 

[PeteWright]

You could paste this code into a separate Module:

Public Function XLfilename() As String
   XLfilename = ThisWorkbook.Name
End Function

Then just use it as a regular formula anywhere you want, like that:

MrExcel_2025-03.xlsm
	H
1	MrExcel_2025-03.xlsm
2	MrExcel_2025-03.xlsm
3	MrExcel_2025-03.xlsm
4	MrExcel_2025-03.xlsm
5	MrExcel_2025-03.xlsm
6	MrExcel_2025-03.xlsm
7	MrExcel_2025-03.xlsm
8	MrExcel_2025-03.xlsm
9	MrExcel_2025-03.xlsm
10	MrExcel_2025-03.xlsm
11	MrExcel_2025-03.xlsm
12	MrExcel_2025-03.xlsm
13	MrExcel_2025-03.xlsm
Sheet1

Cell Formulas
Range	Formula
H1:H13	H1	= XLfilename()

Would that be okay for you?

 

[iggydarsa]

Sub InsertFileNameInColumnH()
  Dim ws As Worksheet
  Dim lastRow As Long
  Dim fileName As String
  Set ws = ActiveSheet
  fileName = ThisWorkbook.Name
  lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Change "A" to your desired column to find last row
  If lastRow > 0 Then
    ws.Range("H1:H" & lastRow).Value = fileName
  End If
End Sub

 

[poikl]

Hi Iggy thanks for replying but it's copying the Workbook name (Personal.xlsm) instead of the individual File name (Prd.xlsx).
Can you please adapt that it use the File name

 

[PeteWright]

Hi Iggy thanks for replying but it's copying the Workbook name (Personal.xlsm) instead of the individual File name (Prd.xlsx).
Can you please adapt that it use the File name

It depends where you put your code.
If, for example, you put your macro in your Personal Workbook it will write the Personal Workbook's name, but if you put it in your individual File it will write that name.

Use this adapted code instead:

Sub InsertFileNameInColumnH()
  Dim ws As Worksheet
  Dim lastRow As Long
  Dim fileName As String
  Set ws = ActiveSheet
  fileName = ActiveWorkbook.Name
  lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row 'Change "H" to your desired column to find last row
  If lastRow > 0 Then
    ws.Range("H1:H" & lastRow).Value = fileName
  End If
End Sub

Although this method isn't fail-safe* it should provide the correct result in most cases.
*) ActiveWorkbook addresses the currently "active" or focussed Workbook while ThisWorkbook addresses the Workbook containing the VBA code

 

[poikl]

Thanks Pete, now it actually showed the correct desired Work sheet name (PRD) but only on ROW1 and didn't extend down the column.

 

[poikl]

Pete, sorry I didn't change "K" to "A" as you had noted in the macro. Once I changed that it worked perfectly as needed. Thanks again.