Autofill a formula to last row on multiple sheets at once

[kimmas82]

I've searched and searched and couldn't find a solution for what I'm trying to do.

I have multiple sheets that are the exact same (90 sheets). I need to populate a formula in cell e4 on each of the sheets and autofill the forumla to the last row of data on each sheet. I'm looking for a vba solution. I've seen many solutions for doing this on one tab and I can get that, but i need/want to do it on all of the sheets at the same time. Is this possilbe?

This is an example of what I have:

Sheets(Array("Apple", "Banana", "Cucumber", "Pear", "Orange")).Select
Range("E4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]="""","""",IF(ISERROR(VLOOKUP(RC[-4],WRAP!R2C2:R42C3,2,FALSE)),1,(VLOOKUP(RC[-4],WRAP!R2C2:R42C3,2,FALSE))))"
Range("E4").Select
lastrow& = Range("A:d").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("E4").Autofill Destination:=Range("e4:e" & lastrow)

I apologize if this has been ask3ed/answered before, but I did search and couldn't find exactly what I was looking for.

[nemo74]

How does this work for you? Best I could come up with, since you didn't attach a sample file.

Please Login or Register  to view this content.

[Armando Montes]

Might try selecting all sheets and aplly formula:

Please Login or Register  to view this content.

[kimmas82]

Sorry, it doesn't work. I get an "Autofill Method of Range Class Failed" error on the last line. I get the same with the coding you provided.

If I specify the last row as in the sample below where I specify to row 300, the coding works fine. it's just when I try to add coding to populate to the last row that I get that error.

ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets(Array("Apple", "Banana", "Cucumber", "Pear", "Orange")).Select
Sheets("Apple").Activate
Range("E4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]="""","""",IF(ISERROR(VLOOKUP(RC[-4],WRAP!R2C2:R42C3,2,FALSE)),1,(VLOOKUP(RC[-4],WRAP!R2C2:R42C3,2,FALSE))))"
Range("E4").Select
Selection.AutoFill Destination:=Range("E4:E300"), Type:=xlFillDefault
Range("E4:E300").Select

[nemo74]

without a sample sheet, I don't know what else to tell you. The code that I provided works perfect on a blank sheet. It copies your formula to all cell E4 to all worksheets in the workbook and then copies it down to the last row in column E on each sheet.

It will error out if all of E is blank on any sheet.

[kimmas82]

Ok. . . I hope I attached correctly. It took me a minute to put an example together.

[nemo74]

OK,
try this on your sheet. Per your example it works fine as long as there is not 1 row of data on a sheet.

Please Login or Register  to view this content.

[kimmas82]

I do have some sheets that will only have one row of data after the headers. Does that mean this will not work on those sheets or it won't work at all? Also, I do have three sheets, WRAP and two others, that do not need this formula in column E. Does that change the coding?

[nemo74]

yes that changes the coding.

You can fix the header and 1 row by adding

Please Login or Register  to view this content.

You can also take part of my orignal code and hide the sheets you don't need prior to running the macro.