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).ListInde x))
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