Autofill

Hello,

below is a part of the code im using. I want this code to be flexible enough so that any range of data will work with it. Thus I want the autofill part to not be limited to N2:n1860. I may have data thats only N2:n100 or n2:n3000.

Selection.AutoFill Destination:=Range("N2:N1860")
Range("N2:N1860").Select
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O1860")
Range("O2:O1860").Select
Columns("O:O").Select
Selection.Style = "Percent"
Columns("N:N").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Columns("N:N").EntireColumn.AutoFit
Range("M:M,K:K,J:J,F:F").Select

What code would I use to popup the save as window at the end of my code

Thanks for the help

hello,

Try this... it should allow the user to select the range to be auto filled,

Selection.AutoFill Destination:=Range(Application.Selection)
Selection.AutoFill Destination:=Range(Application.Selection.Offset(rowOffset:=0, columnOffset:=1))
Columns("O:O").Select
Selection.Style = "Percent"
Columns("N:N").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Columns("N:N").EntireColumn.AutoFit
Range("M:M,K:K,J:J,F:F").Select

if the other lines after the auto fill statement need to be modified then you need to update the range references based on the selection

Application.Selection.Style = "Percent"

and

Application.Dialogs(xlDialogSaveAs).Show

Will display the save as window.

Thanks for your reply. I put the code you had into my code and here is how part of it looks.

Sheets("Variables").Select
range("P1").Select
Application.CutCopyMode = False
Sheets("Stats").Select
range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-8]-(RC[-4]+RC[-3]+RC[-1]+(RC[-5]*R1C9))"
range("B42").Select
Sheets("Variables").Select
range("Q1").Select
Sheets("Stats").Select
range("O2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-9]"
range("N2").Select

Selection.AutoFill Destination:=range(Application.Selection)
Selection.AutoFill Destination:=range(Application.Selection.Offset(rowOffset:=0, columnOffset:=1))
Columns("O:O").Select
Selection.Style = "Percent"
Columns("N:N").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Columns("N:N").EntireColumn.AutoFit
range("M:M,K:K,J:J,F:F").Select

Application.Selection.Style = "Percent"

range("F1").Activate
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-"" _);_(@_)"
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-"" _);_(@_)"
range("M:M,K:K,J:J,F:F").EntireColumn.AutoFit
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0

I'm not sure what "Application.Selection.Style = "Percent" is for. Am I supposed to put this into the code as above

Right now I get en error "method 'range' of object '_global' failed.

More help needed. Thanks

Hello again,

The code you had originally referenced two columns N and O. You changed that in the autofill statements but you still have code that references columns N and O, for example.

Columns("O:O").Select
Selection.Style = "Percent"

Perhaps that's what your needing to do in your code or perhaps it's not, I don't know....

I was just pointing out that just as you changed the N and O references in the autofill statement then, if you need to do the same with the rest of the code, then continue to use Application.Selection as the range.

If on the other hand your code always changes column O to percentage then you don't need to use Application.Selection.Style.

I'm a little confused as to what i should do. I appreciate you patience as i am a novice at this stuff. What I did is replaced my code with the code you gave me and i had the problems as I stated in the previous post. Below is the same piece of script as the one above with out any changes.

Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H1860")
Range("H2:H1860").Select
Columns("H:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B:C").Select
Selection.ColumnWidth = 10.71
Range("B23").Select
Sheets("Variables").Select
Range("A1:O1").Select
Selection.Copy
Sheets("Stats").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Variables").Select
Range("P1").Select
Application.CutCopyMode = False
Sheets("Stats").Select
Range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-8]-(RC[-4]+RC[-3]+RC[-1]+(RC[-5]*R1C9))"
Range("B42").Select
Sheets("Variables").Select
Range("Q1").Select
Sheets("Stats").Select
Range("O2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-9]"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N1860")
Range("N2:N1860").Select
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O1860")
Range("O2:O1860").Select
Columns("O:O").Select
Selection.Style = "Percent"
Columns("N:N").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Columns("N:N").EntireColumn.AutoFit
Range("M:M,K:K,J:J,F:F").Select

I resolved my problem using...thanks for all the help.

Dim DestinationRng, OriginalRng As Range
    Dim end_row As Integer
    end_row = Sheets("stats").Range("a65536").End(xlUp).Row
    Set DestinationRng = Sheets("stats").Range("H2:H" & end_row)
    Set OriginalRng = Range("H2")
    OriginalRng.AutoFill Destination:=DestinationRng, Type:=xlFillDefault