VBA to copy paste some filtered columns to new sheet (dynamic table)

Re: VBA to copy paste some filtered columns to new sheet (dynamic table)

Option Explicit


Public Sub Copy_Columns()




Dim r As Range
Dim lLast_Row1 As Long
Dim lLast_Row2 As Long
'identify the columns and copy the data
With Worksheets("Raw").ListObjects(1).DataBodyRange
    Set r = Application.Union(.Columns(1), .Columns(2), .Columns(7))
End With
With Worksheets("Output")
    r.Copy .Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
    
'extend the formulas
    lLast_Row1 = .Range("E" & Rows.Count).End(xlUp).Row
    lLast_Row2 = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("E" & lLast_Row1).Resize((lLast_Row2 - lLast_Row1) + 1, 6).FillDown
    
End With


End Sub

Regards
Ger

Re: VBA to copy paste some filtered columns to new sheet (dynamic table)

Quote

1. This works in copy and pasting the entire set of data, however if the data in Raw is filtered, the macro still pastes the full 14 rows. Is there a way to edit such that it only copies the filtered data?

2. Can the vba also incorporate the function of clearing the pasted columns before running the copy-and paste process? E.g. When I run the macro a second time, the information is currently pasted in new rows below those pasted from the first run.

Point 1 is done. I wasnt clear on point 2... As I read it the first part, you want to clear previously pasted data, this is what the code below does. So you get a fresh view each time. Your example seems to contradict this, but I'm not sure if your example is from the code as it was in post number #2, or how you would like it to be Anyway, let me know if the code is right.

Also, if for example, I select "Banks", there is only 1 row to copy (fine), but the formulas in columns E:J are not being cleared... so you could have 8 or 10 rows of formulas from the last time you run it. If you want to clear the formulas too then change

Set r2 = .Range("B8:D8").Resize(.Range("B6").CurrentRegion.Rows.Count)

to

Set r2 = .Range("B8:J8").Resize(.Range("B6").CurrentRegion.Rows.Count)

Option Explicit
 
Public Sub Copy_Columns()
     
     
    Dim r As Range
    Dim r2 As Range
    Dim lLast_Row1 As Long
    Dim lLast_Row2 As Long
     'identify the columns and copy the data
    With Worksheets("Raw").ListObjects(1).DataBodyRange
        Set r = Application.Union(.Columns(1), .Columns(2), .Columns(7)).SpecialCells(xlCellTypeVisible)
    End With
    With Worksheets("Output")
        Set r2 = .Range("B8:D8").Resize(.Range("B6").CurrentRegion.Rows.Count)
        
        r2.Clear
        r.Copy r2.Cells(1, 1).Offset(-1)
         
         'extend the formulas
        lLast_Row1 = .Range("E" & Rows.Count).End(xlUp).Row
        lLast_Row2 = .Range("B" & Rows.Count).End(xlUp).Row
        
        If lLast_Row2 > lLast_Row1 Then
            .Range("E" & lLast_Row1).Resize((lLast_Row2 - lLast_Row1) + 1, 6).FillDown
        End If
         
    End With
     
End Sub

Re: VBA to copy paste some filtered columns to new sheet (dynamic table)

Assuming you have kept this line

Set r2 = .Range("B8:D8").Resize(.Range("B6").CurrentRegion.Rows.Count)

Then the formulas are Not overwritten. So if you change the formula on some rows to fixed percentages, then they should be retained when you rerun the macro.

Its only in cases where the number of copied / Pasted rows ("B:D") EXCEEDS the number of rows with formulas that I "filldown" the formulas based on the LAST row that contains formulas ("E:J"). Other than that, I dont touch the formulas in "E:J"

Regards
Ger

Re: VBA to copy paste some filtered columns to new sheet (dynamic table)

In the short term, change back this line:

Set r2 = .Range("B8:J8").Resize(.Range("B6").CurrentRegion.Rows.Count)

to

Set r2 = .Range("B8:D8").Resize(.Range("B6").CurrentRegion.Rows.Count)

In the longer term you will need to include the formula as part of the logic in the VBA.

I would suggest starting a new thread on this, because ultimately the problem you are trying to solve right here in this thread has been solved

In the new thread you should try and describe the formula you want to inject into the rows... shouldnt be that hard to do it. The formula should be generic enough to fit your business needs... for example, under what conditions would you change a cell from a formula to a fixed value. The formula should be generic enough to account for those instances. As I said, start a new thread

Regards
Ger

Re: VBA to copy paste some filtered columns to new sheet (dynamic table)

You could probably do this yourself... Typically its just a case of including extra criteria on the filter... for example, if "Amount" or "Years" or "Yield" had a "#" error in the them, then you would simply apply a filter on those fields for "Number" and "Greater Than" and then select 0 as the value. This will hide any "#" errors in that column and my vba code will only copy the visible filtered rows.

I could that do that in VBA too (apply a filter >0), but I would need to know in advance which column contains (or is likely to contain) the # error.

Regards
Ger

Re: VBA to copy paste some filtered columns to new sheet (dynamic table)

That shouldnt matter... any given field (column) can filter for two criteria...

Alternatively, just copy the data to the new table (including #values or #errors) - as we are doing above anyway, and then apply the filter to the new table > 0 to hide the rows with # values

(Or got a step further and filter the new table for the character "#" and delete those rows from the new table)

Ger