Automatically Populating fields based on 1 criteria

[kelmack]

Hi,

I have two worksheets in the same book, where the 2nd sheet is my data and I want the first sheet to automatically populate the cells if I enter a value in the 2nd sheet.

Eg.Sheet 2

Column A Column B Column C Column D Column E Column F
Code Description Price Total Qty Boat 1 Boat 2

40000 TC Angel Shark $12.90 50 20 30
40001 TC Blue Eye
40002 TC Ling $10.90 60 60

On sheet 1, I basically want to populate cells with the above information only if there is a value in column D, blank values need to be skipped, so that I get a summary of sheet two for all items where there is a value in Total Qty.

 

[John Davis]

Hi,

I have two worksheets in the same book, where the 2nd sheet is my data and I want the first sheet to automatically populate the cells if I enter a value in the 2nd sheet.

Eg.Sheet 2

Column A Column B Column C Column D Column E Column F
Code Description Price Total Qty Boat 1 Boat 2

40000 TC Angel Shark $12.90 50 20 30
40001 TC Blue Eye
40002 TC Ling $10.90 60 60

On sheet 1, I basically want to populate cells with the above information only if there is a value in column D, blank values need to be skipped, so that I get a summary of sheet two for all items where there is a value in Total Qty.

Does this help?

Sub Kelmack()
'
'
Dim lr As Long

lr = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("Sheet2")
'
    .Range("D1:D" & lr).AutoFilter Field:=1, Criteria1:="<>"
    .Range("A1:G" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet1").Range("A1")
    .Range("D1:D" & lr).AutoFilter
    
End With

End Sub