Combine data of some cells in multiple excel files into one sheet

**ricdamiani** · 08-06-2013, 06:36 AM

Originally Posted by lotuxel

Hi rocdamiani,
Pls see below and you have to put all the files to one file's sheets and Goal sheet is target sheet.

Sub jw_test()
Dim wb As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim re As Variant
Dim i As Long
Dim j As Long
Dim rng As String
   
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Goal")
j = 2
For Each ws1 In wb.Worksheets

   If ws1.Name <> "Goal" Then
         ws.Cells(j, 1) = ws1.Name
      For i = 0 To 100
            re = Array("C3", "C5", "C8", "D8", "E8", "D9", "G13", "H13", "F22", "F24", "F26", "F29", "F31", "F34", "F40", "D43", "E43", "F46", "D49", "E49", "F52", "F55", "C58", "C59", "C60", "C61", "C62", "")
            rng = re(i)
            If rng = "" Then Exit For
                ws.Cells(j, i + 2) = ws1.Range(rng).Value
     Next i
     End If
     j = j + 1
Next ws1
End Sub

Hi lotuxel,
Thank you for your reply.

Sorry but I didn't understand very well.
I thought the macro was going to open each excel file in the folder and copy those cells into the goal sheet.
Is that happening in the macro? Or how it works? I couldn't understand very well your explanation before the code

Looking forward to hear from you

**lotuxel** · 08-06-2013, 10:02 PM

Originally Posted by ricdamiani

Hi lotuxel,
Thank you for your reply.

Sorry but I didn't understand very well.
I thought the macro was going to open each excel file in the folder and copy those cells into the goal sheet.
Is that happening in the macro? Or how it works? I couldn't understand very well your explanation before the code

Looking forward to hear from you

Yes!, I try to add the purposes :

'assign the active workbook
Set wb = ActiveWorkbook
'assign ws to target sheet
Set ws = wb.Worksheets("Goal")
j = 2
'loop all the sheets in active workbook
For Each ws1 In wb.Worksheets
'if the sheet (ws) 's name is not Goal
   If ws1.Name <> "Goal" Then
      'put the sheet name to goal sheet start from row no. 2 and col A
         ws.Cells(j, 1) = ws1.Name
       'loop the all required range start from C3
      For i = 0 To 100
            re = Array("C3", "C5", "C8", "D8", "E8", "D9", "G13", "H13", "F22", "F24", "F26", "F29", "F31", "F34", "F40", "D43", "E43", "F46", "D49", "E49", "F52", "F55", "C58", "C59", "C60", "C61", "C62", "")
            rng = re(i)
               'if all range finsihed
            If rng = "" Then Exit For
            'place the data (value) of target sheet value from the range from from array
                ws.Cells(j, i + 2) = ws1.Range(rng).Value
     Next i
     End If
     'prepare for next row in goal sheet
     j = j + 1
     'start to next sheet

hope you got the idea.