• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I want to add file Names in Each Sheet

Abhijeet

Active Member
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
 

Attachments

  • Sheet Wise Collaction Macro.xlsm
    14.5 KB · Views: 6
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
Code:
Set WKB = Workbooks.Open(fldpath.SelectedItems(i))

Add in a bit that's something like
Code:
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
 
File Name as string not problem how to paste these on each Sheet that file Name that is problem
Code:
ThisWorkbook.Sheets(shtnames(j)).Range("AA2" & ":" & "AA" & w).Value = WKB.Name
I tried this but not work

So please tell me
 
Hi

I tried and its work perfect for me but this method is correct or not please tell me
Code:
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
 
Back
Top