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.
rdy123 rdyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

Could you send a dummy?

Regards
0
rdy123 rdyAuthor Commented:
0
ShumsExcel & VBA ExpertCommented:
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

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rob HensonFinance AnalystCommented:
You don't need specific VBA for that. With an autofilter in place, pasting to only the visible cells will be the default behaviour.

If you select a single cell to copy and then select the range where required, pasting will only affect the visible cells.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rdy123 rdyAuthor Commented:
Thank you Shums.

But it is selecting B7 cell instead of visible cell after filter.
0
ShumsExcel & VBA ExpertCommented:
Please upload your sample as you mentioned initially filtering header row 6 and where is your formula?
0
rdy123 rdyAuthor Commented:
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.
0
Rob HensonFinance AnalystCommented:
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.
0
ShumsExcel & VBA ExpertCommented:
What is your formula you are using?
0
rdy123 rdyAuthor Commented:
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.
0
ShumsExcel & VBA ExpertCommented:
Not sufficient info.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.