Copy data from many files.

C

Corall

New Member
Joined
Sep 30, 2019
Messages
21
Howdy,

How can I copy data from multiple files (between 2 and 5 files) from a folder on my desktop?
Every day I have to copy data, which are not arranged identically in each file, from sheet1 (each file has only one sheet), of each file found in the WORK folder.
In a file the data can be in the range C3: BA175, in another, in the range F5: AZ220 and so on (the first data row is the column name, C3: BA3 or F5: AZ5 etc ...).
I would like to copy that data (the data in each file will be copied in separate sheets, in the file where I run the VBA code).
Since the data is not identical in all files, I would like the "Name" column (which can be a rice column) to be copied to column A (starting with A2) and then all other columns.

Ex: In file 1, the data is in Range C3: BA175, the column "NAME" is - let's say - in Column D (in C3: BA3 are headers, and in D3 is "NAME"). This column will be copied to column A (A2), of each sheet (from the centralizing file) and then the other columns will be copied to B2: BA2.
If in file 2 the data is in the range F5: AZ220 and the column "NAME" is - let's say - in K5, the data will be copied to another sheet, but the column "NAME" will be copied to column A (A2).

Very complicated.
If someone can help me...
Thank you in advance.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Hi @★ Corall , welcome to the forum.

Let's start whit this.

Change C:\books\, by the name of your folder.

Code: 
Sub Copy_data()
  Dim wFiles As Variant, wFolder As String
  Dim wb1 As Workbook, wb2 As Workbook, sh2 As Worksheet, sh1 As Worksheet
  Dim lr As Long, lc As Long, f As Range
  '
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set wb1 = ThisWorkbook
  wFolder = "[COLOR=#ff0000]C:\books\[/COLOR]"
  If Right(wFolder, 1) <> "\" Then wFolder = wFolder & "\"
  If Dir(wFolder, vbDirectory) = "" Then
    MsgBox "Folder does not exist"
    Exit Sub
  End If
  wFiles = Dir(wFolder & "*.xls*")
  Do While wFiles <> ""
    Set wb2 = Workbooks.Open(wFolder & wFiles)
    Set sh2 = wb2.Sheets(1)
    Set f = sh2.Cells.Find("NAME", , xlValues, xlWhole)
    If Not f Is Nothing Then
      lr = sh2.Cells(Rows.Count, f.Column).End(xlUp).Row
      lc = sh2.Cells(f.Row, Columns.Count).End(xlToLeft).Column
      sh2.Range(f, sh2.Cells(lr, lc)).Copy
      wb1.Sheets.Add after:=wb1.Sheets(wb1.Sheets.Count)
      Set sh1 = wb1.ActiveSheet
      sh1.Range("A2").PasteSpecial xlPasteValues
    End If
    wb2.Close False
    wFiles = Dir()
  Loop
  Application.ScreenUpdating = True
  MsgBox "End"
End Sub
 
Upvote 0
Hi,

DanteAmor,

Thanks for the welcome and for the code, but the code only copies the data range from the "Name" column to the right. I need to copy all the data, including those to the left of the "Name" column.
The code should look for the column with the name "Name", copy it and put it in column A (starting with A2) and then copy the other columns (from B2 to the last column).
In the files where it is copied, there is just that data and nothing else.

Thank you very much for your help.

























<tbody>
</tbody>
 
Upvote 0
In the archives there is always information in column A?
 
Upvote 0
In the archives there is always information in column A Sheet1. Nothing will be copied in Sheet1
But all data will be copied in new sheets created, starting with sheet2..sheet3...
The copied data will be added in blank sheets, which will be created depending on how many files will be in the "books" folder. One sheet for each file.
 
Upvote 0
Try this

In file 1, the data is in Range C3:BA175 (If as you comment in post #5 there is always data in column A, then the range is A3:BA175)

the column "NAME" is in Column D. This column will be copied to column A (A2), and then the other columns will be copied to B2:BA2
Click to expand...

If you want to copy the columns on the left and also those on the right, but you don't mention in your example what to do with column D.


In the following macro, following your example, column D is copied and pasted into column A and all columns from A to BA are copied and pasted starting at column B. (Column D will be repeated.)

Code: 
Sub Copy_data()
  Dim wFiles As Variant, wFolder As String
  Dim wb1 As Workbook, wb2 As Workbook, sh2 As Worksheet, sh1 As Worksheet
  Dim lr As Long, lc As Long, f As Range
  '
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set wb1 = ThisWorkbook
  wFolder = "C:\trabajo\books\"
  If Right(wFolder, 1) <> "\" Then wFolder = wFolder & "\"
  If Dir(wFolder, vbDirectory) = "" Then
    MsgBox "Folder does not exist"
    Exit Sub
  End If
  wFiles = Dir(wFolder & "*.xls*")
  Do While wFiles <> ""
    Set wb2 = Workbooks.Open(wFolder & wFiles)
    Set sh2 = wb2.Sheets(1)
    Set f = sh2.Cells.Find("NAME", , xlValues, xlWhole)
    If Not f Is Nothing Then
      lr = sh2.Cells(Rows.Count, f.Column).End(xlUp).Row
      lc = sh2.Cells(f.Row, Columns.Count).End(xlToLeft).Column
      wb1.Sheets.Add after:=wb1.Sheets(wb1.Sheets.Count)
      Set sh1 = wb1.ActiveSheet
      sh2.Range(f, sh2.Cells(lr, f.Column)).Copy
      sh1.Range("A2").PasteSpecial xlPasteValues
      sh2.Range(sh2.Cells(f.Row, "A"), sh2.Cells(lr, lc)).Copy
      sh1.Range("B2").PasteSpecial xlPasteValues
    End If
    wb2.Close False
    wFiles = Dir()
  Loop
  Application.ScreenUpdating = True
  MsgBox "End"
End Sub
 
Upvote 0
Try to explain better:

The data range is not the same in the copy sheets.
Let's say that in file 1, sheet1 (each file would have only one sheet) the data range is C3: K14 (this is just an example, the range is much larger), in range C3: K3 is a "Name" header.
This column with the name "Name" will be copied into a newly created sheet, in column A (beginning with A2), and the other columns will be copied in columns B to I. No blank columns will be copied.
ex:
[TABLE="width: 239"]
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1308;"> <col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1564;"> <col width="30" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1052;"> <col width="43" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1564;"> <col width="39" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1393;"> <tbody>[TR]
[TD="width: 27, bgcolor: transparent"]asd[/TD]
[TD="width: 37, bgcolor: transparent"]dfsdf[/TD]
[TD="width: 29, bgcolor: transparent"]ghfj[/TD]
[TD="width: 44, bgcolor: transparent"]rtwert[/TD]
[TD="width: 30, bgcolor: transparent"]ytry[/TD]
[TD="width: 43, bgcolor: transparent"]Name[/TD]
[TD="width: 27, bgcolor: transparent"]sad[/TD]
[TD="width: 44, bgcolor: transparent"]fgdfgd[/TD]
[TD="width: 39, bgcolor: transparent"]ghfgh[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]e[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]f[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]g[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]h[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]i[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]j[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]k[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
</tbody>[/TABLE]

and will be copied from A2 to I13, like this:

[TABLE="width: 288"]
<colgroup><col width="43" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;" span="9"> <tbody>[TR]
[TD="width: 43, bgcolor: transparent"]Name[/TD]
[TD="width: 43, bgcolor: transparent"]asd[/TD]
[TD="width: 43, bgcolor: transparent"]dfsdf[/TD]
[TD="width: 43, bgcolor: transparent"]ghfj[/TD]
[TD="width: 43, bgcolor: transparent"]rtwert[/TD]
[TD="width: 43, bgcolor: transparent"]ytry[/TD]
[TD="width: 43, bgcolor: transparent"]sad[/TD]
[TD="width: 43, bgcolor: transparent"]fgdfgd[/TD]
[TD="width: 43, bgcolor: transparent"]ghfgh[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]e[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]f[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]g[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]h[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]i[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]j[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]k[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
</tbody>[/TABLE]

Thank you.
 
Upvote 0
That is a nice example, with that, not much explanation is necessary.

Please:
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own

Try this:
Code: 
Sub Copy_data()
  Dim wFiles As Variant, wFolder As String
  Dim wb1 As Workbook, wb2 As Workbook, sh2 As Worksheet, sh1 As Worksheet
  Dim lr As Long, lc As Long, f As Range, ic As Long
  '
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set wb1 = ThisWorkbook
  wFolder = "C:\trabajo\books\"
  If Right(wFolder, 1) <> "\" Then wFolder = wFolder & "\"
  If Dir(wFolder, vbDirectory) = "" Then
    MsgBox "Folder does not exist"
    Exit Sub
  End If
  wFiles = Dir(wFolder & "*.xls*")
  Do While wFiles <> ""
    Set wb2 = Workbooks.Open(wFolder & wFiles)
    Set sh2 = wb2.Sheets(1)
    Set f = sh2.Cells.Find("NAME", , xlValues, xlWhole, xlByRows)
    If Not f Is Nothing Then
      lr = sh2.Cells(Rows.Count, f.Column).End(xlUp).Row
      lc = sh2.Cells(f.Row, Columns.Count).End(xlToLeft).Column
      If f.Column = 1 Then
        ic = 1
      Else
        If f.Offset(, -1) <> "" Then
          ic = sh2.Cells(f.Row, f.Column).End(xlToLeft).Column
        Else
          ic = f.Column
        End If
      End If
      wb1.Sheets.Add after:=wb1.Sheets(wb1.Sheets.Count)
      Set sh1 = wb1.ActiveSheet
      sh2.Range(sh2.Cells(f.Row, ic), sh2.Cells(lr, lc)).Copy
      sh1.Range("A2").PasteSpecial xlPasteValues
      Set f = sh1.Cells.Find("NAME", , xlValues, xlWhole, xlByRows)
      sh1.Columns(f.Column).Cut
      sh1.Columns("A:A").Insert Shift:=xlToRight
    End If
    wb2.Close False
    wFiles = Dir()
  Loop
  Application.ScreenUpdating = True
  MsgBox "End"
End Sub
 
Upvote 0
Dante,

You are right!
Thank you for your understanding and for your time.
The example presented was for one file only. In that folder can be between 2 and 5 files. Can you please modify the code to copy the data from all the files (each data copied, in newly created sheets).
Thanks a lot.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
858
Micron
M
M
  • Question Question
Copy File Names to Destination File
Replies
2
Views
316
metalscuba
M
O
  • Question Question
VBA code to merge data from files into one master file
Replies
3
Views
467
jkpieterse
jkpieterse
Y
  • Question Question
Consolidate all values from specific sheets into 1 master excel file
Replies
1
Views
178
Trebor76
T
S
  • Question Question
Gathering Data From Multiple Files in Same Folder in Particular Range
3 4 5
Replies
41
Views
2K
kvsrinivasamurthy
K

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top