Macro for specific column fill with file name

[Satish.digit]

Hi,


I have 50 rown data in one excel sheet from A Column to G Column i would like to filled with 50 rown in column H with the file name.


Can you any one help me on this.


Regards,

Satish.

 

[Luke M]

Can you clarify what is in columns A:G? You mention that you have data there, but then it seems your question is to just put the file name in column H? Which file name? If the one of current workbook, won't all the cells just have the same value?

 

[Satish.digit]

Below are the clarifications :


Can you clarify what is in columns A:G?

- A:G columns has the information till 50 rows like A= Cust Name, B =Costomer Code, C=Payment Method, etc.,


Which file name?

- i need current workbook name should be appear in H column till last row which have the info it should be 40 or 50th row.

Satish.

 

[Luke M]

Put this formula in col H:

=CELL("filename",$A$1)

assuming the workbook has been saved somewhere, this will display the full path name for the workbook. Note you can use the MID and FIND functions if needed to trim this down. Copy formula down as desired.

 

[Satish.digit]

Hi Luke,


Thanks for your time on this.


I have the formula with me but i need macro for this.


Regards,

Satish.

 

[Luke M]

How's this?

[pre]

Sub FillFilename()
Dim LastRow As Integer
Dim fPath As String

'Find the last row
LastRow = Range("A65536").End(xlUp).Row
fPath = ThisWorkbook.FullName

'Fill from row 1 to last row
Range("H1", Cells(LastRow, "H")).Value = fPath
End Sub

[/pre]