How to import a range of cells from a different worksheet [Closed]

Report
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
-
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015
-
Hello,

Please help me to import a range of cells depend on the output cells generated on different worksheets on a page-2(in my folder the different file names are C1, C2, C3, C4,C5 & output is on page-2-(i can rename it as Output-1 to Output-5 if vba requires different page names))

I have name range input in current worksheet which i use as reference to get result on C1, C2, C3, C4, C5 worksheets saved in a directory(C:\Users\MyComp\Desktop\Cables).
Conditions are there are 2 types of results.. it is either range A1:G24 or A1 to N24.(this result was generated from name range input)

i want to import these outputs either using the name ranges or using the result on page-2 of worksheets C1 to C5 .

Output should begin in current workbook, page-4 (renamed as Combined) from Cell A1 to the resultant range i.e from file C1, next continued to the next cell
till there is name range input in current sheet assume it is on page1 R1:R5.

Please help me to solve the problem.. if any alternate method is there please inform..


Sub dir()
Sheets("FROM DIRECTORY, worksheets C1 TO C5").Select

from this worksheet
For Each cell In Range("R1:R5")

Application.Goto Reference:=cell.Value ( ("FROM DIRECTORY, worksheets C1 TO C5").for R1 VALUE (NAME RANGE) find the name range in C1, R2 VALUE(NAME RANGE) find the name range in C2...TILL R5 TO C5)
If Sheets("COMBINED").Range("A1") = vbNullString Then
Selection.Copy Output range to ("COMBINED").Range("A1")
Else
Selection.Output Range("COMBINED").Cells(A1, Columns.Count).End(xlToLeft).Offset(0, 1)
End If
Next cell

End Sub

1 reply

Posts
1
Registration date
Tuesday May 19, 2015
Status
Member
Last seen
May 19, 2015

The only way i do that is to select a whole row and copy/paste,i continue until i've copied al that i need from the sheet.
Posts
27
Registration date
Tuesday November 18, 2014
Status
Member
Last seen
May 21, 2015

thanks eman for your reply..i think i had to mention workbook(different files C1 TO C5 ).. i would have done that if it is only for 5 files. i have about 100 files where i need to copy data from & also if some value changed i have to do that again and again.. that is why i am looking for the solution..
i think it is impossible or very difficult to solve this issue..

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!