Autofill not working

[nathan2314]

Hello,

Can someone tell me why the autofill statements at the very end of this code is not working. It looks ok to me but I think the autofill might not like using cells() in the range() function. But I use range(cells(), cells()) for alot of other things and it works. ???
Everything in the code works except the autofill statements at the very end.
Help! :help
' This determines which 'Accessions' and 'Separation' Values to read into Personnel Availability sheet
index1 = Sheets("Personnel Availability").Cells(1, 1).Value
acc_i = 0
sep_i = 0
Do Until index1 = "Accessions"
index1 = Sheets("Personnel Availability").Cells(1, 1).Offset(acc_i, 0).Value
acc_i = acc_i + 1
Loop
sep_i = acc_i + 1

grade_adjusted = Sheets("Long Term Input Data").range("AC1").Value
If grade = "O2" Then
acc_cell = range("C7")
sep_cell = range("C8")
Else
y = 0
Do Until index1 = "***" & grade_adjusted & "***"
index1 = Sheets("Data by Grade").Cells(1, 2).Offset(y, 0).Value
y = y + 1
Loop
End If
acc_cell = Cells(y + 3, 3).Address
sep_cell = Cells(y + 4, 3).Address

Sheets("Personnel Availability").Cells(acc_i, 2).Value = "=(1+'Long Term Input Data'!$V$2)*'Data by Grade'!" & acc_cell
Sheets("Personnel Availability").Cells(sep_i, 2).Value = "=(1+'Long Term Input Data'!$V$3)*'Data by Grade'!" & sep_cell

'Sheets("Personnel Availability").Cells(acc_i, 2).Select
'Selection.AutoFill Destination:=range(Cells(acc_i, 2), Cells(acc_i, 25)), Type:=xlFillDefault
Sheets("Personnel Availability").Cells(sep_i, 2).Select
With Selection
.AutoFill Destination:=range(Cells(sep_i, 2), Cells(sep_i, 25)), Type:=xlFillDefault
End With

[nathan2314]

ahh i figured it out...I had to write it like this. Why wouldn't it work the other way. I figured since I had already selected a cell in the 'Personnel Availability' sheet that the rest of the range / cellss funtions would automatically look in that sheet. I guess not...
Sheets("Personnel Availability").select
Sheets("Personnel Availability").Cells(sep_i, 2).Select
With Selection
.AutoFill Destination:=Sheets("Personnel Availability").range(Sheets("Personnel Availability").Cells(sep_i, 2), Sheets("Personnel Availability").Cells(sep_i, 25)), Type:=xlFillDefault
End With

[Bob Phillips]

Probably because Sheets("Personnel Availability") was not active.

[mdmackillop]

Hi Nathan
Please use linebreaks to prevent code running off the screen.