AUTO FILL Problem

Hello Everyone,

I am having a problem with some visual basic coding within excel and was wondering if anybody could be of assistance to me?

The formula which I have amended in VB aids excel to take information from many sheets of data and input them into one master sheet. The problem I am having is with an auto fill function which is meant to auto fill formula down a line each time information is inputted.

In one sheet of excel the auto fill function in the VB Module auto fills to the next available cell which is fine. However I have used exactly the same code on another module but it seems to auto fill all the way down the entire sheet. I am baffled to the resins why it is doing this. I am wondering whether it could be a option within excel.

I am stuck at the moment and was informed that this is the place to go with any problems with Excel and VB.

Thanks

Hi,

Could you share with us the specific code for an appropriate answer ...

Cheers
Carim

The code which i have used on both of the sheets is as follows:

Range("B6").Activate
Selection.End(xlDown).Select

endcellN = "N" & ActiveCell.Row
endcellQ = "Q" & ActiveCell.Row
endcellR = "R" & ActiveCell.Row
endcellS = "S" & ActiveCell.Row
endcellT = "T" & ActiveCell.Row


'Fill formulas as required
Range("N6").Select
ActiveCell.Formula = "=ROUND(DAYS360(K6,M6,TRUE)/30,0)"
Selection.AutoFill Destination:=Range("N6", endcellN)


Range("Q6").Select
ActiveCell.Formula = "=(Q5+P6)"
Selection.AutoFill Destination:=Range("Q6", endcellQ)

Range("R6").Select
ActiveCell.Formula = "=(C6*P6)"
Selection.AutoFill Destination:=Range("R6", endcellR)

Range("S6").Select
ActiveCell.Formula = "=(S5+R6)"
Selection.AutoFill Destination:=Range("S6", endcellS)

Range("T6").Select
ActiveCell.Formula = "=(S6/Q6*100)"
Selection.AutoFill Destination:=Range("T6", endcellT)


'Close and save "TOTALS.xls" spreadsheet

Workbooks("TOTALS.xls").Close SaveChanges:=True


'Replace "TOTALS.xls" spreadsheet in original directory
Filename = "\\TOTALS.xls"
Set fso = CreateObject("Scripting.FileSystemObject")
Set mainfile = fso.GetFile(Filename)
mainfile.Move "\\TOTALS.xls"

Application.DisplayAlerts = True


End Sub

I dont undersatnd why it has filled only one line on one sheet and down to the bottom of another one.

Does this make sense?

Thanks

Hi,

In the sheet where you have experienced a problem, go test with
End Home the last used cell ... most probably you will have to delete several blank rows to adjust your last used cell ...

HTH
Cheers
Carim