VBA to avoid - "indirect" Formula

[leopaulc]

Dear All,

I want to compare huge database from different sheets according to the sheet name.

Problem faced..
1. From the selection dropdown, the sheet name has to be selected, and it should come to the sheet (Data1, Data2 ....) for the graph reference data.
2. I am using INDIRECT formula to pull the data from relevant sheet. The file became heavy by 2 MB with 5 data sheets.

Requirement....
I need, the entire data sheet to be copied from the selection -controlled by dropdown menu - (specified sheet range..) that is a must - VBA required for copy the data....

I am attaching the sample file for reference.


Please help..

Regards,
Leo Paul

[Admin]

Hi Leo,

Try this code.

Option Explicit
Sub kTest()
    
    Dim cboDDowns() As DropDown
    Dim i   As Long
    Dim c   As Long
    Dim wksSource   As Worksheet
    Dim wksDest     As Worksheet
    
    With Worksheets("Model Selection")
        c = .DropDowns.Count
        If c Then
            ReDim cboDDowns(1 To c)
            For i = 1 To c
                Set cboDDowns(i) = .DropDowns(i)
            Next
        End If
    End With
    
    For i = 1 To c
        Set wksSource = Worksheets(cboDDowns(i).List(cboDDowns(i).ListIndex))
        Set wksDest = Worksheets("Data" & i)
        wksDest.Range("a6:u" & wksDest.UsedRange.Rows.Count).ClearContents
        With wksSource
            .Range("a5:u" & .Range("a" & .Rows.Count).End(xlUp).Row).Copy wksDest.Range("a6")
        End With
    Next
    
End Sub

[leopaulc]

Yes....

Exactly what i required....

Thank you very much....

Regards,

Leo Paul