retain copyto formatting with advanced excel filter

[bazbuyer]

Hi

I may have done this by accident so I think it can be done, but now I am trying to do it again I can't!

I have a master list/register which I want advance filter to produce certain records onto another sheet. That sheet then has an additional 2 fields (columns) per row where the user adds more data, one of the fields is a criteria. I then want to refilter that to another sheet where the criteria Y/N determines if all the rows go to the next sheet. All of this I can do.

But (and here is where I need help) when it goes into the final sheet I want that to be formatted in a pre-determined way so as user can copy the 'table' as a picture and paste into a powerpoint slide. If I set the final destination area as a preformatted table it either doesn't work or loses the formatting. I don't mind adding a macro to format after the data is in the final table (as there will be some macros needed in any event) but you also don't seem to be able to format an existing block as a table or at least a macro wouldnt be able to do it as it wouldnt know how many rows to select. I am sure I got this to work with advanced filter, range/table names where the final table expanded to accommodate whatever the filter through at it and maintained its (the destination's) format

 

[AFPathfinder]

Welcome to the forums!

I don't believe the advanced filter will provide formatting, so you would need to preformat the area if the range is fixed, or format after the fact manually or with a macro.

If the range size is going to be variable, you can account for that in the macro by using something like this:

Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous

You can also use variables to define the boundaries of your data:

Dim lastRow As Integer
Dim lastCol As Integer

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Range("A1:" & Cells(lastRow, lastCol).Address(0, 0)).Borders.LineStyle = xlContinuous
With Range("A1:" & Cells(1, lastCol).Address(0, 0))
    .Interior.Color = 8210719
    .Font.Color = 16777215
    .Font.Bold = True
End With

Also, advanced filter is pretty easy to do in VBA, so you may be able to combine the actions into a single macro.

Sheets("Sheet1").Columns("M:U").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("B1:J9"), CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=False

 

[bazbuyer]

thanks, yes the range will be variable so I can't pre-format, I was hoping to filter into an expanding table. The final output is in a table format (so rows are banded/alternately shaded) and what I am trying to automate is someone manually typing that information from another spreadsheet into a table then saving it as a Powerpoint slide. Because it goes into another pack I am trying to retain the existing format of that pack (because people don't like change). If you format it all as a table after performing the filter the bands start after the filtered data. So I would have to shade bands manually I think?

 

[AFPathfinder]

You can turn the range into a table and pass it a TableStyle that includes banding. I use the macro recorder to provide that syntax and just make a few changes to make it dynamic:

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = _
        "PPTable"
    ActiveSheet.ListObjects("PPTable").TableStyle = "TableStyleLight2"

You can also provide your own banding without using a table:

Sub TableBanding()
    Dim i As Integer
    Dim finalRow As Integer

    finalRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To finalRow Step 2
        Range("A" & i & ":K" & i).Interior.Color = 15849925
    Next i
End Sub

 

[bazbuyer]

Great thanks, I'll give this a go