excel - GetData from multiple Workbooks in the same folder [correct function to add column with wbook name] -

Usually I never ask anything more because I always get some questions and have answers, mostly In matters, great

So, this is the code below which I am using, available on the Internet and not made by me.

This is working well but I want to left or right columns in the data to be extracted, this data is a simple workbook name, it is simple ... my knowledge is very much about Vba Basic, and also thinking that this should be an easy answer, I could not do it so far

  as string as Public strFileName Workbook Public dataWB as Workbook Public strCopyRange as string Sub GetData (as public currentWB) dim strWhereToCopy string, strStartCellColName string dim strListSheet string , StrCLSheet = "list" error as the strCopySheet string as Goto error sheets (Stellisthet). Selection Range ("B2"). 'This is the main loop, we will open files one by one and copy their data Set Masterdata sheet to current WB = ActiveWorkbook while ActiveCell.Value & lt; & Gt; "StrfileName = ActiveCell.Offset (0, 1) & ActiveCell.Value strCopyRange = ActiveCell.Offset (0, 2) & amp;": "& amp; Active; ActiveCell.Offset (0, 3) strWhereToCopy = ActiveCell.Offset 0, 4) .Value strCopySheet = ActiveCell.Offset (0, 6) .Value strStartCellColName = Middle (ActiveCell.Offset (0, 5), 2, 1) application. Workbooks.Open strFileName, UpdateLinks: = False, ReadOnly: = in True data sets WB = ActiveWorkbook sheets (Strokopishet). select range (Strokopirenjh). Choose select. copy the current code. sheets activate (Stroveyrtocopi). LastRow = LastRowInOneColumn (strStartCellColName) to select cells some MsgBox "looks like this: ( LastRow + 1, 1) .Select Selection.Paste Special xlPasteValues, xlPasteSpec IalOperationNone Application.CutCopyMode = False dataWB.Close false sheet (strListSheet) .Select ActiveCell.Offset (1, 0). Exit Exit Selection Loop The sub ErrH file was missing. The data copy operation is not complete. Find the last used row in the "Last Functions LastRowInOneColumn (Col)" column from the subdivision subcollection: 'Column A' in this example http://www.rondebruin.nl/last.htm Long with Dim lastRow LastRow = with ActiveSheet = line, number, color). End (XLEEP). LastRowInOneColumn = Row End With Last Line End Function  

If I understand correctly, you want the name of the source file in column A, while the file The Eta column starts in B? That should do:

  Sub GetData () Dim strWhereToCopy string, strStartCellColName string as dim strListSheet string, strCopySheet string strListSheet = "list" In the form of error as Goto ErrH sheets (strListSheet). Selection range ("B2"). 'This is the main loop, we will open files one by one and copy your data into the masterdata sheet. Turn on WB = ActiveWorkbook while ActiveCell.Value & lt; & Gt; "StrfileName = ActiveCell.Offset (0, 1) & ActiveCell.Value strCopyRange = ActiveCell.Offset (0, 2) & amp;": "& amp; Active; ActiveCell.Offset (0,3) strWhereToCopy = ActiveCell.Offset 0, 4) .Value strCopySheet = ActiveCell.Offset (0, 6) .Value strStartCellColName = Mid (ActiveCell.Offset (0, 5), 2, 1) application. Workbooks.Open strFileName, UpdateLinks: = False, ReadOnly: = True Set Data WB = ActiveWorkbook Sheet (Strokopirate) Select Range Select the selection Copy the current code Activate Sheets (StrawberryTokopy) Select LastRow = LastRowInOneColumn (strStartCellColName) 'Paste data starts in column # 2 (B) cells (lastRow + 1, 2) .Select Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone Application.CutCopyMode = false only population range (cells paste file name in column 1 (A) for all rows (LastRow + 1,1), cells (LastRowInOneColumn (StrStartCellColName) , 1) .Value = strFileName dataWB.Close false sheet (strListSheet) .Select ActiveCell.Offset (1, 0) .Select Exit Exit Loop sub ErrH: MsgBox "It looks like some files were missing the data copy operation is not complete . Exit sub-sub all  

The key I have two lines of comment - Paste starting in column 2 instead of 1, then fill the column 1 with the last known use with the filename. Line up the current last used line In the example I was a little lazy and pasted the full path, but only the name of the SE and the whole of Google's path paths are extracting the filename.

In addition, I followed the pattern by selecting , select , but if you modify the code to eliminate all , then and ActiveCell references, it will run very fast.