Fill Column with Workbook Name Using Wild Card

Hello Excel Expert,

I found the below code in this forum and with little amendment I brought it into my work.

Sub CombineAll()
    Application.ScreenUpdating = False
    Dim MAlrow, MElrow, MSlrow As Long
    Dim Fpath As String
    Dim Fname As String
    MAlrow = Sheets("Arrangment").Range("A65336").End(xlUp).Row
    MElrow = Sheets("Request").Range("A65336").End(xlUp).Row
    MSlrow = Sheets("Status").Range("A65336").End(xlUp).Row
    Fpath = "C:\Users\ImUser\Desktop\Testingsss\" ' change to your directory
    Fname = Dir(Fpath & "*.xlsx")
    Do While Fname <> ""
         
        Workbooks.Open Fpath & Fname
        Worksheets("Arrangment").Activate
        Range("A2", Selection.End(xlDown)).Select '' This assumes header in first row
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy Destination:=Workbooks("Master.xlsm").Sheets("Arrangment").Cells(MAlrow + 1, 1)
         
        Worksheets("Request").Activate
        Range("A2", Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy Destination:=Workbooks("Master.xlsm").Sheets("Request").Cells(MElrow + 1, 1)
         
        Worksheets("Status").Activate
        Range("A2", Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy Destination:=Workbooks("Master.xlsm").Sheets("Status").Cells(MSlrow + 1, 1)
        Workbooks(Fname).Close SaveChanges:=False
        Fname = Dir
         
         ''' Lets get the last row for the next copy/paste '''
        MAlrow = Sheets("Arrangment").Range("A65336").End(xlUp).Row
        MElrow = Sheets("Request").Range("A65336").End(xlUp).Row
        MSlrow = Sheets("Status").Range("A65336").End(xlUp).Row
         
    Loop
    Application.ScreenUpdating = True
End Sub

What I need is that it starts copying data from Column "B2" and in Column "A2" it fills the name of Workbook using wildcard. Like if the file name is
"ABCDEFGH-XXXXXXX-XXXXXXX-DF-memory-insight". then i need it fills "ABCDEFGH" in the column A parallel with the number of records.
If the last used row is "B2:B15" then it should fill the worksheet name using wild card from range "A2:A15".

Can anyone help me with this!

Dear AlphaFrog,

Thank you for this..

I run the macro and ran into the following problem;

The macro start pasting the data from row number 2 along with headers. In my source file row 1 has headers,and in the master workbook i have already define the same headers according to the source file.

I tried to change it but can't get where do i make change in it..... Can you please look into this

And a question for my understanding how many worksheets I can maximum add into the array,[For Each ws In .Sheets(Array("Arrangment", "Request", "Status"))] as there are more worksheets' data need to be copied in the master workbook.may be around 60 worksheets from 60 workbook.


Thanks again for your code and cooperation!

Hello AlphaFrog,

Many thanks again.

I updated the code as per your instruction replacing the piece of code with provided one but got an issue.

The macro do not copy the entire columns of source file into the master worksheet.

What I understand in my source files there are headers but no data in it so I think (but not sure) macro taking this as an empty column and not copying the data after the column once it finds the empty columns.


If you please have your kind consideration on this.



Thank you so much AlphaFrog!

Dear AlphaFrog,

In order to send you the example files I prepared and fills with data keeping some column entirely empty.Ran the macro to test it.

But amazingly it copied the data after empty columns as well.I really couldn't figure out how this time macro is copying all of the data.

In my original file I tried approx 5-6 times and every time macro were not copying the data after empty column.

Then I again pasted the entire vba code and it ran successfully, I don't know what was going wrong at that time.I will have to keep eye on this portion because I rely on this code if anything went wrong then my all efforts will ruin.that i have previously put into it to reduce my daily laborious activities.

I am completely new to vba but striving to read and write codes, the sample code i pasted here in my first thread, Though little bit but I understand that vba program what is the flow and what one line of code is doing. I changed and make amendment in it according to my needs....

But unfortunately you're vba code is beyond to my head, seems advanced level of vba programming.I can hardly understand few lines your vba code.I am still trying to understand what is "UsedRange" in your vba..what it does actually......


However, since two days, I am struggling with a line of code, and trying to find a way that how do i incoroporate the piece of code that allows me to copy data as values (pastespecial) and do not copy data with formatting...

I know its syntax it is something like this .PasteSpecial (xlPasteValues) but don't know where it to put..


I am working with this file in order to automate my daily hectic job.I started it with name of GOD.There are many complications in it, large data set, hundrends of files each file contains hundreds of worksheets and I want to gather all these informations in one file keeping all specific conditions and criteria in mind.So far with the help of you people it is going on as it is to be....Lets see what happens.But I would like to THANK YOU, you have been a very big help to me.

Blessings to your home!

Thanks again