I want to add file Names in Each Sheet

[Abhijeet]

Hi

I have macro for sheet wise consolidate the files but i want to add in Column AA the File Names from where this copy the data can you please tell me how to add in this macro

 

[chirayu]

You need an extra variable to store the workbook name after you open it.

So the bit in your macro where you've got the below

Set WKB = Workbooks.Open(fldpath.SelectedItems(i))

Add in a bit that's something like

WKBName = Workbooks(fldpath.SelectedItems(i)).Name

Then later on when you're pasting your data just paste your WKBName variable too

WKBName should be a String variable

 

[Abhijeet]

File Name as string not problem how to paste these on each Sheet that file Name that is problem

ThisWorkbook.Sheets(shtnames(j)).Range("AA2" & ":" & "AA" & w).Value = WKB.Name

I tried this but not work

So please tell me

 

[Abhijeet]

Hi

I tried and its work perfect for me but this method is correct or not please tell me

For j = LBound(shtnames) To UBound(shtnames)
        For Each wks In WKB.Sheets
            If wks.Name = shtnames(j) Then
                w = WKB.Sheets(shtnames(j)).Range("a65356").End(xlUp).Row
                    If w >= 2 Then
                        WKB.Sheets(shtnames(j)).Range(stcol & "2:" & lastcol & w).Copy _
                            Destination:=ThisWorkbook.Sheets(shtnames(j)).Range("a65356").End(xlUp).Offset(1, 0)
LastCell = ThisWorkbook.Sheets(shtnames(j)).Cells(Rows.Count, "A").End(xlUp).Row
LastCell2 = ThisWorkbook.Sheets(shtnames(j)).Cells(Rows.Count, "AA").End(xlUp).Row + 1
                            ThisWorkbook.Sheets(shtnames(j)).Range("AA" & LastCell2 & ":" & "AA" & LastCell).Value = WKB.Name

 

[Abhijeet]

Hi

Can you please tell me above method is correct or not

 

[Derek McGill]

If it works / Does what you want does it matter if its correct or not ???

 

[Abhijeet]

method is imp if this is not good or time taking


so i ask it is correct or not