Hi, I have an existing vba see attached and code below that copies and paste columns from one sheet to another. The destination sheet has some existing columns that does a Filldown everytime the macro is run.
Sub copypaste()
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:J8").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
I have an existing Column E in the 'Output' tab, that has a formula =100%/(COUNTIF(b:b, ">0") applied. This ensures that the % allocation is equally split by default.
However, sometimes a user may overwrite the cells in Column E with his/her own preferred portfolio allocation. Assuming the user overwrites all the formulas in Column E, my existing macro will not be able to apply the COUNTIF formula anymore as it is all overwritten..
How can i build the formula into the vba such that it will repply the formula everytime the macro is run (see attached file)?
thank you!