vba auto fill issue

[kelvin_9]

Hello All,

i have some data at sheet1 that i use code to divert in different columns at sheet3 and auto fill down to last row if any
however this will come with an error #1004 on below columns IF i have only 1 row data
what should i do to avoid any of this case?

    Sheets("Sheet3").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+2"
    Selection.AutoFill Destination:=Range("B2:B" & Range("F" & Rows.Count).End(xlUp).Row)
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "987654"
    Selection.AutoFill Destination:=Range("D2:D" & Range("F" & Rows.Count).End(xlUp).Row)
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Japan"
    Selection.AutoFill Destination:=Range("E2:E" & Range("F" & Rows.Count).End(xlUp).Row)
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(Sheet2!RC[12],"" "",Sheet2!RC[11])"
    Selection.AutoFill Destination:=Range("G2:G" & Range("F" & Rows.Count).End(xlUp).Row)

thank you very much for your help

 

[kelvin_9]

I don't quite understand what you are trying to do with this part here:

    'fill date
    Lr = WS_3.Range("A" & Rows.Count).End(xlUp).Row
    WS_3.Range("A" & Lr, WS_3.Range("B" & Rows.Count).End(xlUp).Offset(, -1)).FillDown

Firstly, we already calculated "Lr" earlier using column F. Can we still use this last row value, or does it need to be re-calculated again?
Since we seem to be trying to fill column A, that would seem to suggest that column A is blank, so I don't understand how we can then use column A to find the last row to fill down to.
What column should we look at to determine exactly how far down we need to fill column A?

base on column F to fill down column A
like your initial answer, base on column F to fill down column B, D, E, or G
However, i think there is only 1 row data copy from sheet 2, so column A can not fill down and get A2 filled as A1

thank you very much

 

[kelvin_9]

sample here:

Book31
	A	B
1	Ship out date	Consignee's_Request Date
2	Ship out date	#VALUE!
3
4
limited home

 

[Joe4]

If you are entering the date manually in cell A2, then get rid of the new "lr" calculation, and then use this formula:

If lr > 2 Then
    WS_3.Range("A3:A" & lr).Value = WS_3.Range("A2").Value
End If

 

[kelvin_9]

If you are entering the date manually in cell A2, then get rid of the new "lr" calculation, and then use this formula:

If lr > 2 Then
    WS_3.Range("A3:A" & lr).Value = WS_3.Range("A2").Value
End If

Joe4
thank you very much for your reply

first, this is just prefect and do what i want exactly
second, i am deeply sorry being messes, complicated and my poor presentation of my bad english

thank you

 

[Joe4]

You are welcome.