VBA to avoid - "indirect" Formula

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

Hi Leo,

Try this code.

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

---

Yes....

Exactly what i required....

Thank you very much....

Regards,

Leo Paul