Results 1 to 3 of 3

Thread: VBA to avoid - "indirect" Formula

  1. #1
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0

    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
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    Yes....

    Exactly what i required....

    Thank you very much....

    Regards,

    Leo Paul

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Reversing a "First Middle Last" Name to "Last, First Middle" Name Format
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-06-2014, 10:04 PM
  3. Replies: 5
    Last Post: 04-18-2013, 02:30 AM
  4. Follow-up to "Excel Number Format: Indian Style Comma Separation"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 04-14-2012, 10:46 PM
  5. Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 03-20-2012, 03:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •