Copy specific cells from multiple worksheets in multiple workbooks into a new workbook

[nextin]

Hello, i want to extract specific cells from specific worksheets in multiple workbooks into a new "master" workbook. Each workbook has the same template, thus each cell is in the same place in the different workbooks.

E.g. I want to copy cells nr. D39, L34 and C16 from worksheet "Detaljplan" and cells nr. F14, G14, I14, J14, M14 and N14 from worksheet "Dim PEX og HL"

These values should now be copied into a new row in the new workbook. The cells from the next original workbook should now be row nr. 2 and so on.

All of the original workbooks is in the same folder

I have found a thread i a different forum describing almost the same problem: http://en.allexperts.com/q/Excel-1059/2008/2/Copy-cells-worksheet-multiple.htm

Thank you in advance

Lars

 

[hiker95]

nextin,

Sample data, in your new workbook, sheet "Summary", before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	*	*	*	*	*	*	*	*	*	*
2	*	*	*	*	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*	*	*
Summary

Sample data in one of two workbooks created for testing:

Excel Workbook
	C	D	E	F	G	H	I	J	K	L
16	C16 1	*	*	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*	*	*	*
33	*	*	*	*	*	*	*	*	*	*
34	*	*	*	*	*	*	*	*	*	L34 1
35	*	*	*	*	*	*	*	*	*	*
38	*	*	*	*	*	*	*	*	*	*
39	*	D39 1	*	*	*	*	*	*	*	*
Detaljplan

Excel Workbook
	F	G	H	I	J	K	L	M	N
14	F14 1	G14 1	*	I14 1	J14 1	*	*	M14 1	N14 1
Dim PEX og HL

The second test workbook contains the same data, except the data ends in "2".



After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	*	*	*	*	*	*	*	*	*	*
2	D39 1	L34 1	C16 1	F14 1	G14 1	I14 1	J14 1	M14 1	N14 1	nextin1.xls
3	D39 2	L34 2	C16 2	F14 2	G14 2	I14 2	J14 2	M14 2	N14 2	nextin2.xls
4	*	*	*	*	*	*	*	*	*	*
Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Press the keys ALT+Q to exit the Editor, and return to Excel.

Option Explicit
Sub GetMyData()
Dim myDir As String, fn As String, sn As String, sn2 As String, n As Long, NR As Long

'***** Change Folder Path *****
'myDir = "C:\TestData"  'for testing
myDir = "C:\TestData"

'***** Change Sheetname(s) *****
sn = "Detaljplan"
sn2 = "Dim PEX og HL"

fn = Dir(myDir & "\*.xls")
Do While fn <> ""
  If fn <> ThisWorkbook.Name Then
    With ThisWorkbook.Sheets("Summary")
      NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
      
      'D39, L34 and C16 from worksheet "Detaljplan"
      With .Range("A" & NR)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!D39"
        .Value = .Value
      End With
      With .Range("B" & NR)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!L34"
        .Value = .Value
      End With
      With .Range("C" & NR)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!C16"
        .Value = .Value
      End With
      
      'F14, G14, I14, J14, M14 and N14 from worksheet "Dim PEX og HL"
      With .Range("D" & NR)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!F14"
        .Value = .Value
      End With
      With .Range("E" & NR)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!G14"
        .Value = .Value
      End With
      With .Range("F" & NR)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!I14"
        .Value = .Value
      End With
      With .Range("G" & NR)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!J14"
        .Value = .Value
      End With
      With .Range("H" & NR)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!M14"
        .Value = .Value
      End With
      With .Range("I" & NR)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!N14"
        .Value = .Value
        .Offset(, 1).Value = fn
      End With
    End With
  End If
  fn = Dir
Loop
End Sub

Before you run the macro, you will have to change this section of code:
'***** Change Folder Path *****
'myDir = "C:\TestData" 'for testing
myDir = "C:\TestData"

to reflect the correct Folder Path, for example:
myDir = "C:\ExcelFiles"


Then run the "GetMyData" macro.

 

[nextin]

Thank you very much for the reply!

I have a follow up question, maybe I should start a new thread?

Many of the worksheets that I work with is filled manually by operators, because of that valuable data is not in the same cell each time.

My question is then, a macro that you open from the new master worksheet that opens a "open file dialog" where you select the file you want. The selected file then opens and you can select multiple cells that will become a new row in the master workbook.

TYanks in advance!

Lars

 

[hiker95]

nextin,

You should start a new post/thread.

 

[litaskali]

This code works very well for me except I would like to have default "ok" when getting the data into the new spreadsheet. Any suggestions?

 

[hiker95]

litaskali,

I assume that your raw data structure is not the same.

Please do not post your questions in threads started by others - - this is known as thread hijacking.
Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.
Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


When you start your own New Post you should include:

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.