0

I'm learning to avoid using .Select and I just cant figure out how to rewrite my .AutoFill code.

I have two different sheets, one where i collect data from, paste that in the other sheet, and I need the autofill to run on the second sheet without activating it.

This is my working code now, works because I Select the sheet and the cell "A30"

Selection.AutoFill Destination:=Range("A30:M30").Resize(NumRows1), Type:=xlFillDefault

This works perfectly, but I would like to NOT activate the sheet and select the cell.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Thomas Nørlund Rasmussen
  • 27
  • 3
  • Replace `Selection` with the `Range` you've selected. – BigBen May 08 '19 at 13:22
  • The [documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofill) has a good example as well. – BigBen May 08 '19 at 13:29
  • 1
    Possible duplicate of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – TylerH May 08 '19 at 13:34
  • My code today looks like this `Range("A30:M30").Select Selection.AutoFill Destination:=Range("A30:M30").Resize(NumRows1), Type:=xlFillDefault` Now i tried replacing it with this: Worksheets("Temp").Range("A30:M30").AutoFill Destination:=Range("A30:M30").Resize(NumRows1), Type:=xlFillDefault Still no luck... – Thomas Nørlund Rasmussen May 08 '19 at 13:39

1 Answers1

1

To give you a starting point, here are some examples of using autofill on another sheet after copy/paste.

First - Copy the range you are going to use to the other worksheet

Second - Select the range on the worksheet you are going to autofill

Third - When using Resize the number has to be bigger then the range because when selecting a range the focus is on the first cell in the range. e.g. If not greater then Range("A1:A10").resize(5).Select the selected range will be Range("A1:A5"), thus not autofill action.

Different ways to Resize are:

Sheets("Sheet3").Range("A1:B10").AutoFill Destination:=Sheets("Sheet3").Range("A1:B10").Resize(20), Type:=xlFillDefault

Or

Dim lr As Long
lr = Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count

'the lr must be grater then the autofill range

Sheets("Sheet3").Range("A1:B10").AutoFill Destination:=Sheets("Sheet3").Range("A1:B10").Resize(lr), Type:=xlFillDefault

You can also use the With statement

With Sheets("Sheet3").Range("A1:B10")
    .AutoFill Destination:=.Resize(.CurrentRegion.Rows.Count + .CurrentRegion.Rows.Count), Type:=xlFillDefault
End With

'the `.CurrentRegion.Rows.Count' doubles the range for the autofill, you can replace the second one with a specific number e.g. `.CurrentRegion.Rows.Count + 5'
GMalc
  • 2,608
  • 1
  • 9
  • 16
  • Thank you SO much! Perfect examples! Sometimes i can get a bit confused looking at other peoples examples as there is often way more code to suit their needs. I was so close to solve it my selv as my code looked like this: 'Worksheets("Temp").Range("A30:M30").AutoFill Destination:=Range("A30:M30").Resize(NumRows1), Type:=xlFillDefault' I fell a bit dumb, cause now i can see that "all" i left out was in Destination where i missed the Sheet name... Well i learned a lot and now im on the way to learn even more! :) – Thomas Nørlund Rasmussen May 10 '19 at 07:48