How to copy (with a macro) formulas from a row down based on if values exist an that row on another tab.

[exPERten]

Hello again.

I need to copy a row of formulas from a specific row (Row 16) down but only if there is data in a specific filed on another tab.

To explain a little bit better.
Some people fill in data on a specific tab (Tab 2)
Other people fill in data on tab 4

My formulas concatinate and/or copies that data in tab 5

The problem is that sometimes there are only 20 rows of data, and sometimes there are 9999 rows.

If I copy the formulas down manually the file gets to large to be able to send as a mail.
So I would like to be able to add a macro that copies the formulas down to the last row of data based on another tab.

Thanks in advance.

 

[levy77]

You could loop through the worksheets you would like to look at and run an if statement to see if the last row is a greater than the current last row number, once you have looped through each sheet, you will have a variable which will be the row you want to copy down. Then just run another for loop form i=1 to last row.

Basically,

for each ws in sheets
if Range("A1000000").end(xlup) > LastRow then
LastRow = Range("A1000000").end(xlup).row
end if
next.

You should be left with LastRow = the largest row. Then run loop i = 1 to LastRow.

Hope this helps.

 

[exPERten]

That went a bit over my head to be honest.

What I want to do is check how many rows one of the columns on one worksheet has.
And then copy formulas in column A to AS from row 16 down to as many rows of data that specific worksheet had.

Thanks for bearing with me, though.