Autofill

Autofill

• Dat23

  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

  
  

• cblaich

  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

  
  

• BilalShouman

  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

  
  

• Dsg8362

  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.

  
  
  

• prog.gabi

  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.

  
  

  
  
  
• Autofill