Solved: in Excel 2007, pasting to visible cells only

I have an 8700-row static data table with 9 columns. I want to filter the data by product and state and create new dynamic formulas for each/product combination and then copy them down to ONLY the visible rows that have met the same filter criteria.

I checked with the Microsoft Communities Group and was told that Excel does not have a built-in function to paste formulas to visible cells only but that this can be accomplished with VBA code. Could you help me with this?

Thanks in advance

Can you make up and post a sample workbook showing exactly what you are after?

I'll see what I can do. The data table has 8700+ rows and it will take a little thinking to come up with a representative fragment.

I can already tell you that the hidden rows do NOT occur at regular intervals.

PS: OK, here is a representative fragment of the file. Right now the table is filtered for corn. Columns D:J contain static numbers which need to be replaced with dynamic formulas. I want to create formulas in D2:J2 and copy them down to the visible rows only.

Obviously, a manual paste is easy enough in this sample but the real table has 8760 rows.

Quote:

---

Originally Posted by mdmackillop

Can you make up and post a sample workbook showing exactly what you are after?

---

My original reply above has been edited and now contains the sample file that you requested.

Thanks!

What formula would you like in D2 for exampleo?

Probably something like this
[VBA]Sub Copies()
Dim Rng As Range
Dim Fmla As Range
Set Fmla = Range("D2:J2")
Set Rng = Range(Range("D2").Offset(1), Range("D2").Offset(1).End(xlDown)).Resize(, 7)
Fmla.Copy
Rng.SpecialCells(xlCellTypeVisible).PasteSpecial (xlPasteFormulas)
End Sub
[/VBA]

The fragment that I gave you is over-simplified and the actual worksheet is a lot more complex.

Each State/product combination has its own formula, linking to other sections of the workbook with named ranges and INDEX functions. I would enter the formula in cell D2 and then copy it across. Then I would want to paste the formulas of D2:J2 to all visible rows that have met the filtering criteria.

In a real situation, and in the absence of a "smart" solution, I would need to paste the row manually 100-300 times.

If, in light of this clarification, you still suggest the above code, I will certainly give it a try.

Sample containing test formulae

Quote:

---

Originally Posted by mdmackillop

Sample containing test formulae

---

I tested it and it seems to be working fine. What can be added to the code so that it also copies the fill color of D2:J2?

(I shade the starting row light yellow so that I have a visual check of the rows that have been modified)

Many thanks!

You may need to set the colorindex value for your own PC
[VBA]Sub Copies()
Dim Rng As Range
Dim Fmla As Range
Set Fmla = Range("D2:J2")
Set Rng = Range(Range("D2").Offset(1), Range("D2").Offset(1).End(xlDown)).Resize(, 7)
Fmla.Copy
With Rng.SpecialCells(xlCellTypeVisible)
.PasteSpecial (xlPasteFormulas)
.Interior.ColorIndex = 36
.Cells(1).Select
End With
Application.CutCopyMode = False
End Sub
[/VBA]

Fantastic! You are going to save me tons of time. Many thanks:beerchug:
Marking as solved.

Here's a related question. Using the above sample file, what if I wanted to filter the list (Corn for Grain as an example) and copy all the visible values from the 2009 column to the 2008 column. I can't seem to get any combination of pastespecial to work for me. Any ideas? Thank You

Can you post sample data? It seems to have disappeared.