VBA Error in autofill, error 1004, not dragging down numbers

Question

What I'm trying to do is to put a table in a sheet and have a macro autofill the information in a database, but when I get to the point of dragging down numbers (not formulas) in a column I get the error 1004, I have also tried other ways which sometimes won't throw me an error but won't do what I want.

My logic is this: first fill the info from the input sheet to the database sheet (this part works) Then count how many rows the part of the database that was recently populated to count the new number of rows to use as destination (it's counting correctly). Then drag a column to complete the information, this is what isn't working.

Here's the code:

Sub Macro1()
Dim Size As Integer
Dim L As Integer
Sheets("DATABASE").Activate
L = (Range("C11").End(xlDown).Row)
MsgBox (L)
Sheets("Input Sheet").Activate
Size = (Range("B2").End(xlDown).Row) - 2
MsgBox (Size)
**This is filling the info from the input sheet to the database**
Range("B3:C3" & Size).Cut Sheets("DATABASE").Range("C1:D1").Offset(L, 0) 
Range("D3:D3" & Size).Cut Sheets("DATABASE").Range("F1").Offset(L, 0) '
Range("E3:E3" & Size).Cut Sheets("DATABASE").Range("K1").Offset(L, 0) '
Range("F3:F3" & Size).Cut Sheets("DATABASE").Range("AJ1").Offset(L, 0) '
Sheets("DATABASE").Activate
Size = (Range("C11").End(xlDown).Row)
MsgBox (Size)
Range("B11:B11" & L).Select
**This is where I get the error**
Selection.AutoFill Destination:=Range("B11:B" & Size), Type:=xlFillDefault
End Sub

BTW there is a better way of achieving what you want. Find the last row in Column B as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) and then use `Range("B11:B" & lrow).Formula = Range("B11").Formula` — Siddharth Rout, Jan 16 '20 at 16:48
I also tried using that syntax, I forgot to put that one instead of the one I posted. But it does not work either. — Roberto Cuesta, Jan 16 '20 at 16:50
I should have mentioned it, but I'm dragging down numbers in this case. — Roberto Cuesta, Jan 16 '20 at 16:53
Thanks for the observations, I have modified the code to use Long instead of Integer, but to my understanding the difference between them is the range of values that they can have and its very unlikely that this database in particular to get more than 2000 inputs. Is there another reason? — Roberto Cuesta, Jan 16 '20 at 17:15
I commented on your answer also, but perhaps give [this question](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) a read. Basically there is no benefit to using `Integer`. — BigBen, Jan 16 '20 at 18:36

score 0 · Answer 1 · answered Jan 16 '20 at 16:59

Can't believe it took me two hours to find out the error but here it is:

Sub Macro1()
Dim Size As Integer
Dim L As Integer
Sheets("DATABASE").Activate
L = (Range("C11").End(xlDown).Row)
MsgBox (L)
Sheets("Input Sheet").Activate
Size = (Range("B2").End(xlDown).Row) - 2
MsgBox (Size)
Range("B3:C3" & Size).Cut Sheets("DATABASE").Range("C1:D1").Offset(L, 0) 
Range("D3:D3" & Size).Cut Sheets("DATABASE").Range("F1").Offset(L, 0) 
Range("E3:E3" & Size).Cut Sheets("DATABASE").Range("K1").Offset(L, 0)
Range("F3:F3" & Size).Cut Sheets("DATABASE").Range("AJ1").Offset(L, 0) 
Sheets("DATABASE").Activate
Size = (Range("C11").End(xlDown).Row)
MsgBox (Size)
Range("B11:B" & L).Select <---- this range selection is the correct one, on the previous version it had the wrong syntax
Selection.AutoFill Destination:=Range("B11:B" & Size), Type:=xlFillDefault
End Sub

[No need to `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also [use `Long`, don't use `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). — BigBen, Jan 16 '20 at 17:00

VBA Error in autofill, error 1004, not dragging down numbers

1 Answers1