Auto Fill macro

[Alvaroro84]

Selection.AutoFill Destination:=Sheets("Sheet1").Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible), Type:=xlFillDefault

I try to use this to code to auto fill all visible cells in column E. The reason it has to be visible rows is because it runs a filter before the code. However, the code above seem to be wrong can some one help me fix the above code in a similar format if possible

 

[Max1616]

What does lastrow = in your macro?

 

[Alvaroro84]

What does lastrow = in your macro?

Previously this but I've adjust to what you put on there

LastRow = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row

 

[Alex Blakenburg]

In post #1 you used column E, in post #4 you used both D & E, in post #5 you used Column D.
Are you sure you are giving the suggestions in #2 & #3 a fair go. Both suggestions work on column E per post #1 and assume the formula is in E2.

 

[Alvaroro84]

In post #1 you used column E, in post #4 you used both D & E, in post #5 you used Column D.
Are you sure you are giving the suggestions in #2 & #3 a fair go. Both suggestions work on column E per post #1 and assume the formula is in E2.

I did make the proper changes to capture column D

 

[Alex Blakenburg]

The code above is what I'm working with and it does not seem to be doing the job when it fills the data i fills it with numbers and its suppose to fill it with word

You have made this same comment on 2 of the suggestions. If you click on the D3 or below after running what you had in Post #4, do you see a formula in there.
per post #4

ActiveSheet.Range("A1").AutoFilter field:=4, Criteria1:="0"
    ActiveSheet.Range("D2:D" & Rows.count).SpecialCells(xlCellTypeVisible).Range("A1").Select
ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-3],'[ccc]Cos'!R2C3:R400000C4,2,False)"
     
Sheets("Sheet1").Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = Sheets("Sheet1").Range("D2").FormulaR1C1

 

[Alvaroro84]

Is the variable "Lastrow" defined in your macro? Is the sheet this is running on, the active sheet in the workbook? These are the only things I can think of stopping the macro from running.

lastrow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 3 To lastrow
    If Range("E" & i).EntireRow.Hidden = False Then
        Range("E2").Copy Range("E" & i)
    End If
Next i

This one is working but its filling the cells with whatever is in column E2 and not with the first filtered cell

 

[Alvaroro84]

You have made this same comment on 2 of the suggestions. If you click on the D3 or below after running what you had in Post #4, do you see a formula in there.
per post #4

ActiveSheet.Range("A1").AutoFilter field:=4, Criteria1:="0"
    ActiveSheet.Range("D2:D" & Rows.count).SpecialCells(xlCellTypeVisible).Range("A1").Select
ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-3],'[ccc]Cos'!R2C3:R400000C4,2,False)"
    
Sheets("Sheet1").Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = Sheets("Sheet1").Range("D2").FormulaR1C1

I got a run time error '1004'
application defined or object-defined error and it highlights the code below

Sheets("Sheet1").Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = Sheets("Sheet1").Range("D2").FormulaR1C1

 

[Alex Blakenburg]

This one is working but its filling the cells with whatever is in column E2 and not with the first filtered cell

Let's tackle your post #16 comment first. (Max's version)

Like I said before in @Max1616's solution you are putting the Vlookup formula in D2 but running the loop using column E.
Try it again and change the references inside the loop to be D.

lastrow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 3 To lastrow
    If Range("D" & i).EntireRow.Hidden = False Then
        Range("D2").Copy Range("D" & i)
    End If
Next i

 

[Alvaroro84]

Let's tackle your post #16 comment first. (Max's version)

Like I said before in @Max1616's solution you are putting the Vlookup formula in D2 but running the loop using column E.
Try it again and change the references inside the loop to be D.

i adjusted my macro to accommodate column E it looks like

Sub macro2()
Dim lastrow As Long
Dim lcol As Long
Dim crow As Long
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim latestDate As Date
Dim LMD As Date
Dim WB As Workbook
Set WB = ActiveWorkbook
lastrow = Cells(Rows.count, 4).End(xlUp).Row


ActiveSheet.Range("A1").AutoFilter field:=5, Criteria1:="0"
    ActiveSheet.Range("E2:E" & Rows.count).SpecialCells(xlCellTypeVisible).Range("A1").Select
ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-4],'[ccc]Pay'!R2C3:R400000C4,2,False)"
     

For i = 3 To lastrow
  If Range("E" & i).EntireRow.Hidden = False Then
    Range("E2").Copy Range("E" & i)
End If
Next i

 

[Alvaroro84]

Let's tackle your post #16 comment first. (Max's version)

Like I said before in @Max1616's solution you are putting the Vlookup formula in D2 but running the loop using column E.
Try it again and change the references inside the loop to be D.

lastrow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 3 To lastrow
    If Range("D" & i).EntireRow.Hidden = False Then
        Range("D2").Copy Range("D" & i)
    End If
Next i

I got them both working it look like the issue was with
that i had last row defined incorrectly. Unfortunately its not working like the original post I had but it is filling the cells. Thank you for all the help