Pull data from multiple sheets in multiple workbook into specified ranges on multiple sheets on a Master workbook

[pann8888]

Hello!</SPAN>

I am hoping to get some help with consolidating data from multiple sheets of multiple workbooks into different sheets of a “Master” workbook. The users of this Master workbook will only have limited familiarity with Excel, so I will be using this as a way to avoid having them cut and paste data (too much potential for human error). </SPAN>

My intent is to give the users the capability to browse to specific excel workbooks with multiple sheets and have the Master workbook pull specific ranges from them and populate them in designated ranges on separate sheets of the Master workbook. I have macros on sheet 1 (Instruction) that allow users to browse to files of interest (the paths of the selected files are then pasted into a cell on sheet 2, “List”). The part I need help with is the VBA for the data pulls.</SPAN>

I started with a method and some code I found on Chandoo (Consolidate data from different excel files (VBA) | Chandoo.org - Learn Microsoft Excel Online), but I lack the expertise to change it sufficiently to meet my needs. I have had some success in the past with this, but this particular case goes far beyond my limited experience with VBA.</SPAN>

The only other issue with the data pulls is that the users won’t always be browsing to 5 files. Sometimes they’ll only browse to one file, sometimes to two, sometimes to four, and so on. So ideally the whole thing wouldn’t blow up if fewer than 5 full paths are pasted into sheet 2 (“List").</SPAN>

Any help would be greatly appreciated! The following is what Sheet 2 looks like:


[TABLE="width: 1155"]
<TBODY>[TR]
[TD]Item Number</SPAN>
[/TD]
[TD]File Name</SPAN>
[/TD]
[TD]Full Path</SPAN>
[/TD]
[TD]Data Range Sheet</SPAN>
[/TD]
[TD]Data Range Start Cell</SPAN>
[/TD]
[TD]Data Range End Cell</SPAN>
[/TD]
[TD]Copy to Sheet</SPAN>
[/TD]
[TD]Copy To Location (Start Cell Only)</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]SME#1.xlsx</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#1.xlsx</SPAN>
[/TD]
[TD]SME Information!</SPAN>
[/TD]
[TD]$C$7</SPAN>
[/TD]
[TD]$D$17</SPAN>
[/TD]
[TD]MasterSMEbio</SPAN>
[/TD]
[TD]$C$7</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]SME#1.xlsx</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#1.xlsx</SPAN>
[/TD]
[TD]JA Tasks!</SPAN>
[/TD]
[TD]$H$10</SPAN>
[/TD]
[TD]$P$58</SPAN>
[/TD]
[TD]MasterJAtask</SPAN>
[/TD]
[TD]$H$12</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]SME#1.xlsx</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#1.xlsx</SPAN>
[/TD]
[TD]JA KSAs!</SPAN>
[/TD]
[TD]$H$9</SPAN>
[/TD]
[TD]$M$40</SPAN>
[/TD]
[TD]MasterJAksa</SPAN>
[/TD]
[TD]$H$9</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]SME#1.xlsx</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#1.xlsx</SPAN>
[/TD]
[TD]JA Comps!</SPAN>
[/TD]
[TD]$F$15</SPAN>
[/TD]
[TD]$H$43</SPAN>
[/TD]
[TD]MasterJAcomp</SPAN>
[/TD]
[TD]$F$15</SPAN>
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]SME#1.xlsx</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#1.xlsx</SPAN>
[/TD]
[TD]JA SME Comments!</SPAN>
[/TD]
[TD]$B$5</SPAN>
[/TD]
[TD]$B$19</SPAN>
[/TD]
[TD]MasterSMEcomment</SPAN>
[/TD]
[TD]$B$5</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]SME#2.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#2.xlsx</SPAN>
[/TD]
[TD]SME Information!</SPAN>
[/TD]
[TD]$C$7</SPAN>
[/TD]
[TD]$D$17</SPAN>
[/TD]
[TD]MasterSMEbio</SPAN>
[/TD]
[TD]$C$20</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]SME#2.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#2.xlsx</SPAN>
[/TD]
[TD]JA Tasks!</SPAN>
[/TD]
[TD]$H$10</SPAN>
[/TD]
[TD]$P$58</SPAN>
[/TD]
[TD]MasterJAtask</SPAN>
[/TD]
[TD]$Q$12</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]SME#2.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#2.xlsx</SPAN>
[/TD]
[TD]JA KSAs!</SPAN>
[/TD]
[TD]$H$9</SPAN>
[/TD]
[TD]$M$40</SPAN>
[/TD]
[TD]MasterJAksa</SPAN>
[/TD]
[TD]$O$9</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]SME#2.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#2.xlsx</SPAN>
[/TD]
[TD]JA Comps!</SPAN>
[/TD]
[TD]$F$15</SPAN>
[/TD]
[TD]$H$43</SPAN>
[/TD]
[TD]MasterJAcomp</SPAN>
[/TD]
[TD]$I$15</SPAN>
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]SME#2.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#2.xlsx</SPAN>
[/TD]
[TD]JA SME Comments!</SPAN>
[/TD]
[TD]$B$5</SPAN>
[/TD]
[TD]$B$19</SPAN>
[/TD]
[TD]MasterSMEcomment</SPAN>
[/TD]
[TD]$B$22</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]SME#3.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#3.xlsx</SPAN>
[/TD]
[TD]SME Information!</SPAN>
[/TD]
[TD]$C$7</SPAN>
[/TD]
[TD]$D$17</SPAN>
[/TD]
[TD]MasterSMEbio</SPAN>
[/TD]
[TD]$C$33</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]SME#3.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#3.xlsx</SPAN>
[/TD]
[TD]JA Tasks!</SPAN>
[/TD]
[TD]$H$10</SPAN>
[/TD]
[TD]$P$58</SPAN>
[/TD]
[TD]MasterJAtask</SPAN>
[/TD]
[TD]$Z$12</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]SME#3.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#3.xlsx</SPAN>
[/TD]
[TD]JA KSAs!</SPAN>
[/TD]
[TD]$H$9</SPAN>
[/TD]
[TD]$M$40</SPAN>
[/TD]
[TD]MasterJAksa</SPAN>
[/TD]
[TD]$V$9</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]SME#3.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#3.xlsx</SPAN>
[/TD]
[TD]JA Comps!</SPAN>
[/TD]
[TD]$F$15</SPAN>
[/TD]
[TD]$H$43</SPAN>
[/TD]
[TD]MasterJAcomp</SPAN>
[/TD]
[TD]$L$15</SPAN>
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]SME#3.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#3.xlsx</SPAN>
[/TD]
[TD]JA SME Comments!</SPAN>
[/TD]
[TD]$B$5</SPAN>
[/TD]
[TD]$B$19</SPAN>
[/TD]
[TD]MasterSMEcomment</SPAN>
[/TD]
[TD]$B$39</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]SME#4.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#4.xlsx</SPAN>
[/TD]
[TD]SME Information!</SPAN>
[/TD]
[TD]$C$7</SPAN>
[/TD]
[TD]$D$17</SPAN>
[/TD]
[TD]MasterSMEbio</SPAN>
[/TD]
[TD]$C$46</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]SME#4.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#4.xlsx</SPAN>
[/TD]
[TD]JA Tasks!</SPAN>
[/TD]
[TD]$H$10</SPAN>
[/TD]
[TD]$P$58</SPAN>
[/TD]
[TD]MasterJAtask</SPAN>
[/TD]
[TD]$AI$</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]SME#4.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#4.xlsx</SPAN>
[/TD]
[TD]JA KSAs!</SPAN>
[/TD]
[TD]$H$9</SPAN>
[/TD]
[TD]$M$40</SPAN>
[/TD]
[TD]MasterJAksa</SPAN>
[/TD]
[TD]$AC$9</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]SME#4.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#4.xlsx</SPAN>
[/TD]
[TD]JA Comps!</SPAN>
[/TD]
[TD]$F$15</SPAN>
[/TD]
[TD]$H$43</SPAN>
[/TD]
[TD]MasterJAcomp</SPAN>
[/TD]
[TD]$O$15</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SME#4.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#4.xlsx</SPAN>
[/TD]
[TD]JA SME Comments!</SPAN>
[/TD]
[TD]$B$5</SPAN>
[/TD]
[TD]$B$19</SPAN>
[/TD]
[TD]MasterSMEcomment</SPAN>
[/TD]
[TD]$B$56</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]SME#5.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#5.xlsx</SPAN>
[/TD]
[TD]SME Information!</SPAN>
[/TD]
[TD]$C$7</SPAN>
[/TD]
[TD]$D$17</SPAN>
[/TD]
[TD]MasterSMEbio</SPAN>
[/TD]
[TD]$C$59</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]SME#5.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#5.xlsx</SPAN>
[/TD]
[TD]JA Tasks!</SPAN>
[/TD]
[TD]$H$10</SPAN>
[/TD]
[TD]$P$58</SPAN>
[/TD]
[TD]MasterJAtask</SPAN>
[/TD]
[TD]$AR$</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]SME#5.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#5.xlsx</SPAN>
[/TD]
[TD]JA KSAs!</SPAN>
[/TD]
[TD]$H$9</SPAN>
[/TD]
[TD]$M$40</SPAN>
[/TD]
[TD]MasterJAksa</SPAN>
[/TD]
[TD]$AJ$9</SPAN>
[/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]SME#5.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#5.xlsx</SPAN>
[/TD]
[TD]JA Comps!</SPAN>
[/TD]
[TD]$F$15</SPAN>
[/TD]
[TD]$H$43</SPAN>
[/TD]
[TD]MasterJAcomp</SPAN>
[/TD]
[TD]$R$15</SPAN>
[/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]SME#5.xlxs</SPAN>
[/TD]
[TD]C:\Users\Desktop\sample\SME#5.xlsx</SPAN>
[/TD]
[TD]JA SME Comments!</SPAN>
[/TD]
[TD]$B$5</SPAN>
[/TD]
[TD]$B$19</SPAN>
[/TD]
[TD]MasterSMEcomment</SPAN>
[/TD]
[TD]$B$73</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
</SPAN></SPAN>

 

[pann8888]

Update - I think I have most of it, but when I run the code, I get the MsgBox error message (the code works just fine if I change "Sheets(strWhichSheetCopy).Range(strCopyRange).Select" to "Range(strCopyRange).Select". However, I need to be able to specify the sheet from which the data should be pulled. Can anyone take a look at the following and let me know if something obvious jumps out at you?

The offsets look different from what you'd think based on the columns above because I subsequently added columns to specify the paste range and the sheet from which the data should be pulled. I'd upload the sheet itself, but regulations forbid me from doing that. Thanks!

Sub GetData()
Dim strWhereToCopy As String, strStartCellRange As String
Dim strListSheet As String, strWhichSheetCopy As String
strListSheet = “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
strStartCellRange = ActiveCell.Offset(0, 5) & “:” & ActiveCell.Offset(0, 6)
strWhichSheetCopy = ActiveCell.Offset(0, 7).Value
Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
Sheets(strWhichSheetCopy).Range(strCopyRange).Select
Selection.Copy
currentWB.Activate
Sheets(strWhereToCopy).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 one or more files were missing. The data copy operation is not complete.”
Exit Sub
End Sub</SPAN></SPAN>