Copy specific cell range from different workbook to one excel sheet

[r_know]

Hi,

My first query; Is it possible to copy the data from different workbooks for the particular cell in range.
Then copied same in one master excel new workbook; in different columns.

Regards,

Rahul

[mdmackillop]

Enter the workbook names in B1 & C1 . Then assuming the workbooks are open, a macro such as

Sub GetData()
Dim cel as Range
For Each cel In Range("B1:C1")
Workbooks(cel.Value).Sheets(1).Range("A3:A50").Copy cel.Offset(1)
Next
End Sub

[r_know]

How to do, when work-book is close?
I got one VBA Code, which I used for one file name book1.xls and book2.xls.
Code works perfectly but I have to work-out same code for several excel files in one folder then how to do it?

Here in this code facing problem, to add name manually with location of file.

How code can modify for the folder contain Excel Files?

Sub COPYCELLRANGETOANOTHERSHEET()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim WBDest As Workbook
Dim i As Long
Set WBDest = Workbooks("Combine.xls")

'Open up your first workbook, copy data
Set WB1 = Workbooks.Open("C:\Users\Rahul\Desktop\Mac answer\Price combine Mac\book1.xls")
For i = WB1.Sheets.Count To 1 Step -1
WB1.Sheets(i).Range("B5:B400").Copy
WBDest.Sheets("Sheet1").Cells(2, 1).Insert shift:=xlShiftToRight
WBDest.Sheets("Sheet1").Cells(2, 1).PasteSpecial xlValues
Next
'Close first workbook
WB1.Close savechanges:=False
Set WB1 = Workbooks.Open("C:\Users\Rahul\Desktop\Mac answer\Price combine Mac\book2.xls")
For i = WB1.Sheets.Count To 1 Step -1
WB1.Sheets(i).Range("B5:B400").Copy
WBDest.Sheets("Sheet1").Cells(2, 1).Insert shift:=xlShiftToRight
WBDest.Sheets("Sheet1").Cells(2, 1).PasteSpecial xlValues
Next
'Close first workbook
WB1.Close savechanges:=False
End Sub

[mdmackillop]

Do you want every Excel file, or selected ones only. If the latter, you need a list somewhere.

[r_know]

Yes, each excel sheet which located in one Folder.

But I am searching without path indicates; how can be possible?

Means One file, like here combine.xls can be open in same folder and run the macro code, which collect information from different excel files in the same folder for the selected range; which may copy data in to the combine.xls.