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

nextin

New Member
Joined
Jun 24, 2009
Messages
2
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,647
nextin,

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


Excel Workbook
ABCDEFGHIJ
1**********
2**********
3**********
4**********
Summary



Sample data in one of two workbooks created for testing:


Excel Workbook
CDEFGHIJKL
16C16 1*********
17**********
33**********
34*********L34 1
35**********
38**********
39*D39 1********
Detaljplan



Excel Workbook
FGHIJKLMN
14F14 1G14 1*I14 1J14 1**M14 1N14 1
Dim PEX og HL




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



After the macro:


Excel Workbook
ABCDEFGHIJ
1**********
2D39 1L34 1C16 1F14 1G14 1I14 1J14 1M14 1N14 1nextin1.xls
3D39 2L34 2C16 2F14 2G14 2I14 2J14 2M14 2N14 2nextin2.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.

Code:
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

New Member
Joined
Jun 24, 2009
Messages
2
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
 

litaskali

New Member
Joined
Apr 26, 2013
Messages
3
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,647
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.
 

Forum statistics

Threads
1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top