VBA Selection.AutoFill

MrHolmes

New Member
Joined
Jan 23, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Afternoon all,

I really need some help with this one, been on it all day to no avail.
I am looking to build a macro that will copy a range from once sheet and, paste it to another sheet on the next blank row then auto fill down to a destination based on the contents of the adjacent column.

This is what i have:

Sub Alan()
Range("Description").Copy
Sheets("Raw data").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Application.ScreenUpdating = True
Range("Info").Copy
Sheets("Raw data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheets("Raw data").Select
Selection.AutoFill Destination:=Range("A2:D" & Range("E" & Rows.Count).End(xlUp).Row)
Sheets("Form").Select
Range("Description").ClearContents
Range("Info").ClearContents
End Sub

This works fine the first time, but on the next submission it will paste to the next available row however it will not AutoFill and i get the Run-time error '1004': AutoFill method of Range class failed and highlights this section of the code:

Selection.AutoFill Destination:=Range("A2:D" & Range("E" & Rows.Count).End(xlUp).Row)

Any suggestions would be greatly appreciated to save me going insane.

Thanks
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi & welcome to MrExcel.
How about
VBA Code:
Sheets("Raw data").Select
Range("A2:D" & Range("E" & Rows.Count).End(xlUp).Row).FillDown
Sheets("Form").Select
 
Upvote 0
Hi Fluff and thanks for responding.
Unfortunately this did not work. On the second action, the new data pasted was overwritten by the first because its filling down from A2:D each time and not from the recent pasted data.
 
Upvote 0
In that case try
VBA Code:
Range(Range("A" & Rows.Count).End(xlUp).Resize(, 4), Range("E" & Rows.Count).End(xlUp).Offset(, -1)).FillDown
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top