Code to copy down formulas and formatting

[seh0872]

I have a personal project I'm doing with Excel that some VBA code would help with, but I'm not very skilled at writing VBA code. Hoping someone might be able to whip something together quickly for me.

My spreadsheets uses a FILTER function to pull data from external workbooks, after which the data is converted to text. Cells outside the pull range have formulas that act on each row of data ... these formulas are stored in the first row. Once the data is imported via the FILTER function the calculation cells have to be brought down to each row of resulting data.

Because the source data can vary in how many of rows it has, the code has to be dynamic -- copy formula to row x when row x is the last row of data. Obviously, knowing which row is the last row requires looking in a different column.

FWIW, the spreadsheet has 16 worksheets all which pull data from different sources, so the code has to handle all of them. I'm not sure if that means the code has to be specifically built for each worksheet, or if a generic code can be written that can be applied to each worksheet as necessary.

Thanks for any help.

[Mumps1]

It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file. We wouldn't need all the sheets or all the rows. Two or three sheets each with a dozen rows would be fine.

[seh0872]

A super simple file is attached. Remember, on each specific pull the number of rows may be different.

[Mumps1]

Try:

Please Login or Register  to view this content.

[Marc L]

According to the attachment a VBA demonstration as a beginner starter :

Sub Demo1()
         Dim Ws As Worksheet
    For Each Ws In Worksheets
       Range(Ws.[D5], Ws.[A4].End(xlDown)(1, 4)).FillDown
    Next
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[seh0872]

OK guys, I get it. I should have just loaded the actual file itself. The code you've given me certainly will start to get the job done, but I'm not sure I can easily adapt it to my actual file because:

1. The code needs to impact most of the worksheets, but not all (16 of 18)
2. The table structure of each worksheet is not consistent (the columns containing the formulas may be different in each worksheet, as could be the number of columns with formulas).
3. It's more likely to want to apply the code for a group of worksheets (or even a single one) rather than the entire set all at once.

Given this, it seems that I'm going to need to have a module for each worksheet, which are primarily copies of each other with slight modifications. So I'm guessing that:

1. I'll need to strike the For Each ws command and replace with something worksheet specific
2. I'll need to specify the range of columns (which may or may not be contiguous) for which the drop down should occur

The actual file is attached (without its source data) -- if we modify the VBA code you provided to work for the "Civil" tab, I can adapt to the others. In the Civil tab, columns A-C, AA-AD, and AF-AH have the formulas that need to be copied down based on how many rows the dynamic FILTER function in $E4 retrieves (once the filter has done it's job, this data will be copied and pasted as values).

If I'm not overstretching, copying and pasting that data is a nice add-on to the VBA, but it must do so by first copying the formatting of column F (its a conditional format). I figured if I could automate the formula copy down, I'd be OK manually locking the data pulled by the FILTER formula. But if its easy to code....

(Note that any formula that pulls data from an external workbook starts with "xSOURCE". This formula is "activated" by replacing "xSOURCE" with "=" which allows control over when the formula retrieves the data as well as how keeping the size of the file reasonable while in template mode.)

[Marc L]

So just apply FillDown to the range to update in a single codeline …