Index Data

[Utsav]

Hi Dear,

I have a list of I say about 50 sheets ,
In all my sheets G4 contains "Cust id " and C7& C8 contains name and address respectively.

would like to know Can some how I get, Cust Id, Name and address in a single sheet without copying or pasting.
Like

Cust Id Name add

thanx in advance. !

Regards,
Utsav

 

[Sektor]

Sub FF()

    Dim i As Integer, sh As Worksheet
    Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
    
    For i = 1 To 50
        With Worksheets(i)
            ReDim Preserve arr1(1 To i)
            ReDim Preserve arr2(1 To i)
            ReDim Preserve arr3(1 To i)
            arr1(i) = .Range("G4")
            arr2(i) = .Range("C7")
            arr3(i) = .Range("C8")
        End With
    Next
    
    Set sh = Sheets.Add
    sh.Name = "Total"
    With sh
        .Range("A1:C1") = Array("Cust Id", "Name", "Address")
        .Range("A2").Resize(UBound(arr1, 1)) = arr1
        .Range("B2").Resize(UBound(arr2, 1)) = arr2
        .Range("C2").Resize(UBound(arr3, 1)) = arr3
    End With
    
End Sub

 

[Utsav]

Hi,

getting the prompt "Subscript out of Range".

Regards,
Utsav

 

[Sektor]

I see you write "about 50 sheets". As you see I use hard-coded value of 50 in loop. Please, adjust it to real amount of sheets u gonna process.

 

[Utsav]

it is showing all the details of first sheet(means 42 row line items for 1 sheet)
no data for other 41 sheets

 

[Sektor]

Change this line:

    Dim arr1() As Variant, arr2() As Variant, arr3() As Variant

 

[Utsav]

getting the prompt


Rum time Error 13 "Type Mismatch"

 

[Sektor]

Sub FF()

    Dim i As Integer, sh As Worksheet
    Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
    
    For i = 1 To 50
        With Worksheets(i)
            ReDim Preserve arr1(1 To i)
            ReDim Preserve arr2(1 To i)
            ReDim Preserve arr3(1 To i)
            arr1(i) = .Range("G4")
            arr2(i) = .Range("C7")
            arr3(i) = .Range("C8")
        End With
    Next
    
    Set sh = Sheets.Add
    sh.Name = "Total"
    With sh
        .Range("A1:C1") = Array("Cust Id", "Name", "Address")
        .Range("A2").Resize(UBound(arr1, 1)) = WorksheetFunction.Transpose(arr1)
        .Range("B2").Resize(UBound(arr2, 1)) = WorksheetFunction.Transpose(arr2)
        .Range("C2").Resize(UBound(arr3, 1)) = WorksheetFunction.Transpose(arr3)
    End With
    
End Sub

 

[Utsav]

Thanks!!!

God Bless u

 

[Utsav]

Can u insert some function that it automatically recall the number of sheets in the workbook.
so that the manual intervention of counting the sheets can be avoided.


Regards,
Utsav