Macro for Autofill removes header info when there is no data to autofill

Hi
I have seperate worksheets. Data is populated on each sheet and then macro is run to autofill the formula in columns depending on data populated in Column A

However I am now getting to a situation where on one of the sheets, there is no data.

When the macro is run, then the xlup part I assume is now overwriting the header (because there is no data in Column A) this in turn messes up pivots tables and so on.

So I have my header on row 1

What I need to is to modify my macro so that if no data exists in cell A2, then it will move on to the next line. The current code is:

Sheets("xxxx").Select
Range("H2:L2").AutoFill Destination:=Range("H2:L" & Cells(Rows.Count, "A").End(xlUp).Row) - so this works spot on when there is data, however when there is no data the the formula overwrites the header info in cells H1 to L1

I have tried to modify the code but no end of errors. I have searched no end on this forum but cannot find the answer.. I guess it needs something like "If A2 is blank then goto.....else"

Does this help any?

Please Login or Register  to view this content.