Help with VBA

[President]

Hello,

please see the workbook macro solution in Chandoo's web, it is a good code, however it has one problem that i need help with.

there is no column to specify the source sheet from the source workbooks.


Consolidate data from different excel files (VBA) | Chandoo.org - Learn Microsoft Excel Online

see table below, how could the Source sheet be added in the code that would exceprt the data from specific sheets listed in column D highlighted in yellow.

Excel 2010
	A	B	C	D	E	F	G	H
1	Item No	File Name	Full Path	Source Sheet	Data Range Start Cell	Data Range End Cell	Copy To Sheet	CopyToLocation(Start Cell Only)
2	1	abc.xlsx	C:\test\	Sheet1	$A$2	$G$10	MasterData	$A$2
3	2	abc.xlsx	C:\test\	Product	$A$1	$H$50	Sheet1	$A$2
4	3	abc.xlsx	C:\test\	Data	$A$1	$H$50	Sheet1	$A$2
5
6
List

 

[President]

is this code so difficult to amend just to add one addtional column for source sheet?

Sub GetData()
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As StringstrListSheet = “List”On Error GoTo ErrH
Sheets(strListSheet).Select
Range(“B2″).Select
‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> “”
strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
Range(strCopyRange).Select
Selection.Copy
currentWB.Activate
Sheets(strWhereToCopy).Select
lastRow = LastRowInOneColumn(strStartCellColName)
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub
ErrH:
MsgBox “It seems some file was missing. The data copy operation is not complete.”
Exit Sub
End Sub

 

[President]

??