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?
 
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
 
Rgonzo1971Commented:
Hi,

Could you send a dummy?

Regards
0
 
rdy123 rdyAuthor Commented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ShumsDistinguished Expert - 2017Commented:
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
 
rdy123 rdyAuthor Commented:
Thank you Shums.

But it is selecting B7 cell instead of visible cell after filter.
0
 
ShumsDistinguished Expert - 2017Commented:
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
 
ShumsDistinguished Expert - 2017Commented:
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
 
ShumsDistinguished Expert - 2017Commented:
Not sufficient info.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.