Need a macro to pick the details from Tab mentioned one after another

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, cRng As Range, srcWS As Worksheet, desWS As Worksheet, arr As Variant
    Set srcWS = Sheets("Inputs")
    Set desWS = Sheets("Output")
    With srcWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        arr = Application.Transpose(.Range("E4", .Range("E" & .Rows.Count).End(xlUp)).Value)
        For Each rng In .Range("C4:C" & LastRow)
            With Sheets(rng.Value)
                .Cells(1, 1).CurrentRegion.AutoFilter 2, arr, xlFilterValues
                Set cRng = Intersect(.AutoFilter.Range.Offset(1), .Range("A:B,D:E"))
                cRng.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
                .Range("A1").AutoFilter
            End With
        Next rng
    End With
    Application.ScreenUpdating = True
End Sub

Hi Mumps, Thank you so much for your time in helping me on this.

I copy pasted the code in the excel workbook I shared as sample. am getting an error while I run this code...
at "With Sheets(rng.Value)". Am I doing something different.

A couple of addtions though. In output column, may be in the new column, Is there a chance to pick the tab name, from where the data is pulled from, as there are multiple Tabs.

Also based on the Qtr Selection drop down in Inputs, We need to pull only those 3 months of data into Input Tab.
Like if I select Q1,The Plan data should be from Jan to Mar & Actual Data is Jan to Mar.

Just to summarize:
1. I need only the specific columns as mentioned in the input. Eg, I Dont need Sales pull.
2. Also need specific Quarter pull for both Plan & Actual.Attaching the latest template.
3. Need a column in Out put to show the Tab name the record is pulled from.


Appreciate for your quick help on this to me.

An alternative, using ADO/SQL can be found in the attached file.

Note:
1) Due to "Securi" I couldn't post the code.
2) Inputs are hard coded.

For clarification:

Your comment in Inputs says: "Size is not required." However, in your last post you said: "I Don't need Sales pull." Please clarify.

If the sheet names are not listed in the columns of the tables, ADO/SQL can not retrieve the sheet names with this type of SQL statement.

But, if we use a For-Next loop to make a query on each sheet, then sheet names can also be retrieved.

Try the attached file. I have made some minor changes in the source sheets. You no longer need the button. When you make a selection in cell G4, the data will be copied automatically.

You are very welcome.

Hi Mumps1,

I have tried to incorporate the code in the live file and was able to troubleshoot most of it with my little knowledge. 1 place where I am failing is when my Input says only 1 worksheet in C4, then a error thrown at "with Sheets (rng.value)", in the file you have shared with me. When I place 2 or more, I am able to pull it without any error.

Now coming to live file, irrespective of any numbers of worksheets name I give, it always stops at that particicular line to troubleshoot "with Sheets (rng.value)".

Rest of the pull works file, just that it throws me that error. please help me to fix that. I would infact be happy to get a alert that "Pull is successful" once it completes.

P.s: If I exactly give range - For each rng in srcws.range("C4 to C6") it doesnot throw any error. it only throws when I have For Each rng In srcWS.Range("C4:C" & LastRow) as a code.

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G4")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, cRng As Range, srcWS As Worksheet, desWS As Worksheet, arr As Variant, fnd1 As Range, fnd2 As Range
    Dim let1 As String, let2 As String, lRow As Long, x As Long, cnt As Long, mon As String
    Set srcWS = Sheets("Inputs")
    Set desWS = Sheets("Output")
    desWS.UsedRange.Offset(1).ClearContents
    x = Mid(Target, 2, 1)
    If x = 1 Then mon = "Jan"
    If x = 2 Then mon = "Apr"
    If x = 3 Then mon = "Jul"
    If x = 4 Then mon = "Oct"
    Select Case Target.Value
        Case "Q" & x
            With srcWS
                LastRow = .Range("C" & Rows.Count).End(xlUp).Row
                arr = Application.Transpose(.Range("E4", .Range("E" & .Rows.Count).End(xlUp)).Value)
                For Each rng In srcWS.Range("C4:C" & LastRow)
                    With Sheets(rng.Value)
                        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        Set fnd1 = .Range("H1").Resize(, 12).Find(mon)
                        let1 = Replace(Cells(1, fnd1.Column).Address(False, False), "1", "")
                        Set fnd2 = .Range("V1").Resize(, 12).Find(mon)
                        let2 = Replace(Cells(1, fnd2.Column).Address(False, False), "1", "")
                        .Cells(1, 1).CurrentRegion.AutoFilter 2, arr, xlFilterValues
                        cnt = .[subtotal(103,A:A)] - 1
                        Set cRng = Intersect(.AutoFilter.Range.Offset(1), .Range("A:B,D:E"))
                        cRng.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
                        .Range(let1 & "2:" & let1 & lRow).Resize(, 3).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1)
                        .Range(let2 & "2:" & let1 & lRow).Resize(, 3).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "J").End(xlUp).Offset(1)
                        desWS.Cells(desWS.Rows.Count, "E").End(xlUp).Offset(1).Resize(cnt) = Sheets(rng.Value).Name
                        .Range("A1").AutoFilter
                    End With
                Next rng
            End With
    End Select
    Application.ScreenUpdating = True
End Sub

You are very welcome.