Auto populate sheets

[dwool40]

I have a workbook with 5 sheets. On sheet one (INSPECTION), I need the text from column G to populate another sheet if a "Y" is in corresponding row. So, if a "Y" is in D9:D308 I need the text from the corresponding cell in column G to populate sheet 3 (Immediate Action) starting with B3. If a "Y" is in E9:E308, I need the text from the corresponding cell in column G to populate sheet 4 (Deferred Maintenance) starting with B3. If a "Y" is in F9:F308, I need the text from the corresponding cell in column G to populate sheet 5 (60 Day) starting with B3. Hope this makes sense.

 

[mumps]

This macro assumes that you have data in D8:F8.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("INSPECTION").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("INSPECTION").Range("D8:D" & LastRow).AutoFilter Field:=1, Criteria1:="Y"
    Sheets("INSPECTION").Range("G9:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Immediate Action").Range("B3")
    Selection.AutoFilter
    Sheets("INSPECTION").Range("E8:E" & LastRow).AutoFilter Field:=1, Criteria1:="Y"
    Sheets("INSPECTION").Range("G9:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Deferred Maintenance").Range("B3")
    Selection.AutoFilter
    Sheets("INSPECTION").Range("F8:F" & LastRow).AutoFilter Field:=1, Criteria1:="Y"
    Sheets("INSPECTION").Range("G9:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("60 Day").Range("B3")
    Selection.AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[dwool40]

This macro assumes that you have data in D8:F8.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("INSPECTION").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("INSPECTION").Range("D8:D" & LastRow).AutoFilter Field:=1, Criteria1:="Y"
    Sheets("INSPECTION").Range("G9:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Immediate Action").Range("B3")
    Selection.AutoFilter
    Sheets("INSPECTION").Range("E8:E" & LastRow).AutoFilter Field:=1, Criteria1:="Y"
    Sheets("INSPECTION").Range("G9:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Deferred Maintenance").Range("B3")
    Selection.AutoFilter
    Sheets("INSPECTION").Range("F8:F" & LastRow).AutoFilter Field:=1, Criteria1:="Y"
    Sheets("INSPECTION").Range("G9:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("60 Day").Range("B3")
    Selection.AutoFilter
    Application.ScreenUpdating = True
End Sub

Works great when I only do a "y" in one cell. If I do multiple cells (D9, D264, E10, E145, etc) I get a run-time error 1004 "We cant do that to a merged cell)

 

[mumps]

The problem is not the macro, it is the merged cells. You should avoid merging cells at all costs as this almost always creates problems for Excel macros. If you unmerge all the cells, the macro should work properly.