Copy data from different files into a workbook

Shweta

Well-known Member
Joined
Jun 5, 2011
Messages
514
Hi All,


I need a macro that can open all the filesfrom a path provided in a cell in excel work book and copy thecontents from Columns A to K (consolidated data) in the new workbook.Kindly provide me the code to perform this operation, so that I can modify it according to my needs.

The situation is this. I have multiple files stored in D:\MyData
\Sep2011\. I need to have one file (New one with the macro in it),
wherein I have to open this workbook and provide the path in a cell
say C2. Once I provide this path and click the execute macro, I need
the macro to open all the individual workbooks in the path (all
workbooks will be of same format) and copy columns A to K and start
pasting in this new workbook. When the macro goes to the next work
book and opens it, it has to append the data in the next row where the
earlier workbook's data has ended. For e.g. if Workbook A has 50 rows
of data and the macro has copied data frmo R5 to R55, when it opens
the second workbook, it should add the first row from the second
workbook to R56. likewise it has to open all the workbooks,
consolidate the data and put it in. THis is my requirement. i
request you to build the code for this functionality and send me the
code such that i can use it to consolidate 1000s of indiidual files
into one single file.

Thanks,
Shweta
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try this in module

Option Explicit
Sub DataAdd()
Dim RCount As Long
Dim Temp$, Temp1$, Temp2$, Index&, Data As Variant

Temp = "'" & ThisWorkbook.Path & "\[My data.xls]Sheet1'!"
Temp2 = Temp & Columns("A").Address(, , xlR1C1)
Temp2 = "Counta(" & Temp2 & ")"
RCount = Application.ExecuteExcel4Macro(Temp2)
For Index = 1 To RCount
Temp1 = Temp & Cells(Index, 1).Address(, , xlR1C1)
Temp2 = Temp & Cells(Index, 2).Address(, , xlR1C1)
If Application.ExecuteExcel4Macro(Temp1) = Cells(2, 1).Value Then
Data = Data + Application.ExecuteExcel4Macro(Temp2)
End If
Next Index
Cells(2, 2).Value = Data
End Sub
Sub CopyData() '

Dim RCount As Long, CCount As Integer
Dim Temp$, Temp1$, Temp2$, Temp3$, Index&, Num%, Data() As Variant

Temp = "'" & ThisWorkbook.Path & "\[My data.xls]Sheet1'!"
Temp1 = Temp & Rows(1).Address(, , xlR1C1)
Temp1 = "Counta(" & Temp1 & ")"
CCount = Application.ExecuteExcel4Macro(Temp1)


Temp2 = Temp & Columns("A").Address(, , xlR1C1)
Temp2 = "Counta(" & Temp2 & ")"
RCount = Application.ExecuteExcel4Macro(Temp2)


ReDim Data(1 To RCount, 1 To CCount)
For Index = 1 To RCount
For Num = 1 To CCount
Temp3 = Temp & Cells(Index, Num).Address(, , xlR1C1)
Data(Index, Num) = Application.ExecuteExcel4Macro(Temp3)
Next Num
Next Index


Cells(1, 1).Resize(RCount, CCount).Value = Data
Erase Data

End Sub
 
Upvote 0
My coding is: -

Sub consolidatefromdifferentworkbooks()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'On Error GoTo abc
Dim ask As Workbook
Dim ask2 As Workbook
Dim ASK3 As Workbook
Set ASK3 = ActiveWorkbook
Dim i As Long
Dim j As Long
Dim N, z1, r, s, k, d As Long
s = 1
k = 1
Dim sht As Worksheet
Set ask2 = ActiveWorkbook
Sheets(1).Select
Range("A65356").Select
Selection.End(xlUp).Select
r = ActiveCell.Row
Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range("b2").Value
Set ask = ActiveWorkbook
For i = 2 To r
ASK3.Activate
Sheets(1).Select
Workbooks.Open Filename:=Sheets(1).Range("a" & i).Value
Set ask2 = ActiveWorkbook
For d = 1 To ask2.Sheets.Count
Sheets(d).Select
N = Range("A65356").Selection.End(xlUp).Row
If N >= 2 Then
Range("A1:z" & N).Select
Selection.Copy
ask.Activate
ask.Sheets(1).Select
z1 = ask.Sheets(1).Range("A65356").Selection.End(xlUp).Row + 2
Range("A" & z1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ask2.Activate
End If
Next d
ask2.Activate
ask2.Close
ask.Sheets(1).Activate
ActiveWorkbook.Save
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Problem is occuring in the line highlighted in red bold. By running this code files are getting open but data is not getting copied.

Please help.

Regards,
Shweta
 
Upvote 0
how many files and each file which sheets and column you need to look up..
files name and sheets name
 
Upvote 0

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

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