Filter/Copy Paste noncontiguous columns to ws in loop code

[Rowland Hamilton]

Love for help me with this one?

I want to past on the side of my newly created tables, headcount per area captured on my tab '2012HC' and the data in columns B, D, E, G and I from Row 1 to the last row of unfiltered data in column F. The Filter is applied on column F. Don't want to sort or change the source data on tab "2012HC"

I want to past the data into the sheets made from the uniquelist derived from column F for my previous code and into cells starting with K1 to the right and down, columns contiguous.

Now here is my code in progress before doing this part, note: couldn't figure out how to combine these codes into the first For Next loop - Thank you, Rowland:

Sub Add_WS_for_Uniques()
Dim rngUniques As Range, cell As Range, rngSrc As Range, rngDst As Range, ccSrc As Range, ccDst As Range
Dim fmlaSrc As Range, fmlaDst As Range
Dim Lastrow As Long, Firstrow As Long
Dim ws As Worksheet, HCCosts As Worksheet, StaticData As Worksheet
    Set HCCosts = Worksheets("2012HC")
    Set StaticData = Worksheets("Static Data")
    
    Application.ScreenUpdating = False
    
    Lastrow = HCCosts.Range("F" & Rows.Count).End(xlUp).Row
    HCCosts.Range("F1:F" & Lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Set rngUniques = HCCosts.Range("F2:F" & Lastrow).SpecialCells(xlCellTypeVisible)
    HCCosts.ShowAllData
    
    On Error Resume Next
    For Each cell In rngUniques
        If cell.Value <> "" Then
            If Len(Sheets(cell.Value).Name) = 0 Then
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = cell.Value & " Natives"
                    Range("a1").Formula = "Pillar-Ledger"
                    Range("h1").Formula = "LU Col"
                    Range("i1").Formula = "Total P&L"
                    Range("a2").Value = cell.Value
            End If
        End If
    Next cell
    On Error GoTo 0
    
       For Each ws In ActiveWorkbook.Sheets '(Array("",""))
            If ws.Name Like "*Natives" Then
            
        'copy Static Data
              Lastrow = StaticData.Cells(Rows.Count, "C").End(xlUp).Row
              Set rngSrc = StaticData.Range("C1:H" & Lastrow)
 
        'paste Static Data
                Set rngDst = ws.Range("B" & Rows.Count).End(xlUp).Offset(0, 0)
 
                rngSrc.SpecialCells(xlCellTypeVisible).Copy
 
                rngDst.PasteSpecial Paste:=xlPasteValues
                rngDst.PasteSpecial Paste:=xlPasteFormats
                Application.CutCopyMode = False
                
         'copy LU Value
                Lastrow = ws.Range("B" & Rows.Count).End(xlUp).Row
                Set ccSrc = ws.Range("A2")
                
         'paste LU Value
                Set ccDst = ws.Range("A2:A" & Lastrow)
                
                ccSrc.SpecialCells(xlCellTypeVisible).Copy
                
                ccDst.PasteSpecial Paste:=xlPasteValues
                ccDst.PasteSpecial Paste:=xlPasteFormats
                Application.CutCopyMode = False
                
         'Input LU Col and Total P&L Formulas
                ws.Range("h2").Formula = "=IF(ISERROR(MATCH(F2,INDIRECT(""'""&E2&""'!E1:Z1""),0)),0," & _
                "(MATCH(F2,INDIRECT(""'""&E2&""'!E1:Z1""),0)))"
                
                ws.Range("i2").Formula = "=IF(ISNA(SUMIF('2012HC'!F:F,$A2,INDEX('2012HC'!$A:$Z," & _
                ",MATCH($F2,'2012HC'!$A$1:$Z$1,0)))),0,SUMIF('2012HC'!F:F,$A2," & _
                "INDEX('2012HC'!$A:$Z,,MATCH($F2,'2012HC'!$A$1:$Z$1,0))))"
                
          'copy Formulas
                Set fmlaSrc = ws.Range("h2:i2")
                
          'paste Formulas
                Set fmlaDst = ws.Range("h2:i" & Lastrow)
                
                fmlaSrc.SpecialCells(xlCellTypeVisible).Copy
                
                fmlaDst.PasteSpecial Paste:=xlAll
                Application.CutCopyMode = False
                
          'Autofit columns
                With ws.Columns("A:K")
                    .AutoFit
                    .ColumnWidth = .ColumnWidth + 2
                End With
               
                'ws.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2
            End If
         Next ws
    Application.ScreenUpdating = True
End Sub

 

[Rowland Hamilton]

I'm thinking if we can't do this without sorting the source data by column F, let's just sort it. It's only currently sorted in alphabetical order by column B anyway so it can be sorted back.

Thnak you - Rowland

 

[Rowland Hamilton]

Folks: Here is a sample of what I'm working with. I know my range is on the "Static Data" tab but I don't want to rely on that.

Recap: Need to add headcount data to the side on each tab and would like to do all this in the first loop, get rid of the 2nd. Need columns for Name, Ledger, Pillar, Title and Manager. Dummydata-2012 HC Costs Budget .zip

Thank you, Rowland.

 

[Rowland Hamilton]

Folks: Bettter sample, with results per tab as they should be when macro is finished on Natives Sample tab. Dummydata-2012 HC Costs Budget .zip

Also, I don't know if its possible to do this all in 1 loop, since the first filter is for uniques and the next filters would be per Pillar-Ledger. Perhaps 2 loops are needed.

Thank you, Rowland

 

[Rowland Hamilton]

Folks: Okay, I solved my problem for now, but I do want to change it because if an end user labels a sheet "*Natives", the operation will be performed on it as well.

New file sample: Dummydata-2012 HC Costs Budget v2.zip

Thank you - Rowland