VBA Filter from another Tab based on cell value

[garypea123]

Good Morning All,

I am looking for a VBA which will look at the result from D3 (Table Tab) and then filter / copy the corresponding name from E2 onwards (Data Tab) and copy all the applicable data (A:L) under this name and paste into C10 (Table Tab)

For info if the word All is in the managers tab it should copy all data.

Thanks
Gary

 

[garypea123]

Hello Gary,

I've just seen my typo.

In this line:-

wsDT.Range("E3", wsDT.Range("L" & wsDT.Rows.Count).End(xlUp)).Copy wsTT.[C10]

change the E3 to A3.

Cheerio,
vcoolio.

That does work very well - I do not know if there is a way of including the headers in rows 1 and 2. I can create a VBA to copy / paste this in C8 before your macro is run, but did not know if there is a simple way.

 

[garypea123]

Hello Gary,

I've just seen my typo.

In this line:-

wsDT.Range("E3", wsDT.Range("L" & wsDT.Rows.Count).End(xlUp)).Copy wsTT.[C10]

change the E3 to A3.

Cheerio,
vcoolio.

Thanks - And last thing...If I wanted the paste to do formatting and values only? - The paste currently copies over the formulas which I would prefer it not to.

 

[vcoolio]

Hello Gary,

Here's an updated version which will transfer the headings from rows 1 and 2 and will also paste values and formats.

Option Explicit
Sub Test()

        Dim wsTT As Worksheet: Set wsTT = Sheets("Table Tab")
        Dim wsDT As Worksheet: Set wsDT = Sheets("Data Tab")
        Dim NmSearch As String: NmSearch = wsTT.[D3].Value

Application.ScreenUpdating = False
       
        wsTT.[C8].CurrentRegion.Clear
       
          If NmSearch = "All" Then
                wsDT.UsedRange.Copy
                wsTT.[C8].PasteSpecial xlValues
                wsTT.[C8].PasteSpecial xlFormats
          Else
                With wsDT.Range("E2", wsDT.Range("E" & wsDT.Rows.Count).End(xlUp))
                        .AutoFilter 1, NmSearch
                        .CurrentRegion.Copy
                        wsTT.[C8].PasteSpecial xlValues
                        wsTT.[C8].PasteSpecial xlFormats
                        .AutoFilter
                End With
          End If
       
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Cheerio,
vcoolio.

 

[garypea123]

Perfect- Thank you very much

 

[vcoolio]

You're welcome Gary. I'm glad to have been able to assist.

Cheerio,
vcoolio.

 

[garypea123]

I do have one additional question though regarding the 'ALL'

Can I write the VBA so the wsDT is only between say columns A:L? I have a lot of data to the right (which is pulling through when "ALL" is selected.

Dim wsDT As Worksheet: Set wsDT = Sheets("Working Sheet")

 

[vcoolio]

Hello Gary,

Change the "ALL" part of the code to:-

 If NmSearch = "All" Then
                wsDT.UsedRange.Resize(, 12).Copy
                wsTT.[C8].PasteSpecial xlValues
                wsTT.[C8].PasteSpecial xlFormats

Cheerio,
vcoolio.

 

[garypea123]

Hello Gary,

Change the "ALL" part of the code to:-

 If NmSearch = "All" Then
                wsDT.UsedRange.Resize(, 12).Copy
                wsTT.[C8].PasteSpecial xlValues
                wsTT.[C8].PasteSpecial xlFormats

Cheerio,
vcoolio.

Thank you, and just for an example - If I wanted it between say L:Z?(would the below work)

wsDT.UsedRange.Resize(12, 24).Copy

 

[vcoolio]

Thank you, and just for an example - If I wanted it between say L:Z?(would the below work)

wsDT.UsedRange.Resize(12, 24).Copy

Hello Gary,
Ah no. That would be resizing twelve rows and twenty four columns.

You may be after this:-

wsDT.UsedRange.Columns("L:Z").Copy
wsTT.[C8].PasteSpecial xlValues
wsTT.[C8].PasteSpecial xlFormats

Doing this will only give you partial row1 headings (the week days) so perhaps this would suit you better:-

wsDT.UsedRange.Columns("L:Z").Offset(1).Copy

Cheerio,
vcoolio.

 

[garypea123]

Perfect - I try to understand

I would love to do some courses on excel. Its fascinating.