asked on

vba code to paste formula to visible cells only

Hi,

I have a data in which row 6 consists of headings and filter is applied, i want to copy the cell in column B after the criteria is filtered and paste the formula to the visible cells that are in that particular column.

For ex:
row 6 consists of headings filtered for word that consists of "data" in column A and in column B i apply the formula =sum(A+B), now i want to copy this formula and paste to the visible cells that are filtered.

Please suggest.

Rgonzo1971

Hi,

Could you send a dummy?

Regards

rdy123 rdy

ASKER

PFA
Book1.xlsx

Shums Faruk

Doesn't seems your sample workbook matches your requirement, anyway try below code:

Sub CopyPasteFormula()
Dim Ws As Worksheet
Dim LRow As Long
Dim PasteRng As Range
Set Ws = Worksheets("Sheet1")
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set PasteRng = Ws.Range("B7:B" & LRow).SpecialCells(xlCellTypeVisible)
Ws.Range("B7").Copy
PasteRng.PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rdy123 rdy

ASKER

Thank you Shums.

But it is selecting B7 cell instead of visible cell after filter.

Shums Faruk

Please upload your sample as you mentioned initially filtering header row 6 and where is your formula?

rdy123 rdy

ASKER

if we filter for data in column A we can see the formula in column B.

I have made the required changes in the code you sent across.

Thank you again.

Rob Henson

Have you tried without VBA as suggested?

Apply filter on column A, enter formula in column B. Select the cell with formula and the rest of column B as far down as required, press Ctrl + D, the formula will be populated down column B without affecting the hidden rows.

Shums Faruk

What is your formula you are using?

rdy123 rdy

ASKER

Hi Rob,

Thank you. i have tried in excel and its working as well.

But i need in vba, so i asked so.

Thanks again.

Shums Faruk

Not sufficient info.