Macro with Autofill

[PFDave]

I have two lines in my Macro I want some help with:

Selection.AutoFill Destination:=Range("E2:O101")

Selection.AutoFill Destination:=Range("B2:B10")

Is there anyway to ensure that the Autofill completes to everyrow containing data.

When I click autofill manually it fills all the cells down where there is data in the cell to the left of it.

When this macro is run the amount of rows used will differ in both of these autofills.

Anyone able to help me?

Thanks

[arlu1201]

So for the first autofill, the last row should be the last row in D and for the 2nd, it should be A?

[PFDave]

So for the first autofill, the last row should be the last row in D and for the 2nd, it should be A?

Yes that is correct

[arlu1201]

Try this

Please Login or Register  to view this content.

[PFDave]

Olympic Lockdown V2.xls

Sorry I'm useless and VBA, can you add into this for me as when I did it didn't work

[arlu1201]

There are no values in your file, hence it didnt work. You have no data in any of the columns, except headers in column A & B.

[PFDave]

There are no values in your file, hence it didnt work. You have no data in any of the columns, except headers in column A & B.

I copy and paste information into those rows then run the macro, when I pasted a sample into this i have a message regarding autofill.

[arlu1201]

It will be good if you populate the sheet with dummy data in the columns you need, so i can troubleshoot.

[PFDave]

Olympic Lockdown V3.xls

There you go

[arlu1201]

In this sheet A & B columns have data.

But your code is autofilling columns E:O and B. How is that?

[PFDave]

In this sheet A & B columns have data.

But your code is autofilling columns E:O and B. How is that?

A and B do have data, I have opened the attached version and there are 151 rows

[arlu1201]

Yes, thats what i said.

Columns A & B have data. So where do you want the autofill to happen?

[PFDave]

Yes, thats what i said.

Columns A & B have data. So where do you want the autofill to happen?

sorry there are hidden sheets in this

[PFDave]

Sorry can anyone add in the code for me to make this autofill all the way down, my attached sheet V3 have data in the info sheet and there are hidden tabs for when the macro is run.

Thanks

[arlu1201]

I have re-written your code for you, after removing all the select & Activate statements.

Try running this code now and it should work fine.

Please Login or Register  to view this content.

[PFDave]

Thanks Arlu, I'm having trouble changing the code in my macro, is there any chance you could use my demo version, amend the code and resave and attach on here for me to use??

I'm sorry I'm very novice with writing or editing code

[arlu1201]

All you need to do is completely delete your code and paste mine in place.

And then run it.

[PFDave]

It says this action will reset your project and when I do it I lose the yellow Sub Lockdown () at the top , then I receive an error message run time error 1004 Autofill method of range class failed with options end or debug

[PFDave]

It's the second last bit of code which is highlighted yellow when I debug:

Please Login or Register  to view this content.

[arlu1201]

It says this action will reset your project and when I do it I lose the yellow Sub Lockdown () at the top

Sorry, i had missed out on the top line in my earlier code, i have put it in for you now. You can completely replace your code with mine.

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[PFDave]

Sorry, i had missed out on the top line in my earlier code, i have put it in for you now. You can completely replace your code with mine.

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Now I get another error for the new line Runtime Error 9 subscript out of range and again it highlights this new row.

Thanks for sticking with this for me

[arlu1201]

Oops this line

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

[PFDave]

Ok the macro now runs to the end, but it doesn't do what I needed it to do it just copies all 150 rows overs to the lockdown sheet.

[arlu1201]

I ran it at my end and it worked fine. The results should show in the Lockdown Results sheet,right?

See the attached file.

I have made some changes to the code too which is here

Please Login or Register  to view this content.

[PFDave]

Olympic Lockdown V4.xls

I'm clueless as to why mine isn't working then, I've copied this new code into my report and it still just copies the lot and has more than just the lockdown results tab also

[arlu1201]

I ran it here and it works fine.

Are you following these steps -

1. Open the file.
2. Go to View->Macros and run the macro.
3. Incase of any alerts, just click yes.

I have added 2 lines of code before and after your code to stop the alerts from popping up. So ideally, you should just follow steps 1 & 2 above.

[PFDave]

I ran it here and it works fine.

Are you following these steps -

1. Open the file.
2. Go to View->Macros and run the macro.
3. Incase of any alerts, just click yes.

I have added 2 lines of code before and after your code to stop the alerts from popping up. So ideally, you should just follow steps 1 & 2 above.

I open the file, click tools, macros, macros then step into lockdown, copy your code over mine, close VBA and then click on my image to run the macro

[arlu1201]

You do not need to copy the code each time. Once you copy it and save the file, it stays there.

Also, you are following the right steps. Please try again.

Just a clarification - you need the output in "Lockdown Results" right? I see 9 rows of data in Lockdown Results sheet when i run the macro.

[PFDave]

You do not need to copy the code each time. Once you copy it and save the file, it stays there.

Also, you are following the right steps. Please try again.

Just a clarification - you need the output in "Lockdown Results" right? I see 9 rows of data in Lockdown Results sheet when i run the macro.

Yes there should be those 9 rows in Lockdown results, I've saved the macro in the report but it just copies everything over when I run the macro, I'm really confused as to why it worked with you and not with me.

Can you save a copy with the macro updated but not run the macro please? If you attach it and I save to my documents, run the macro with figures in there it might work on my computer and I'll have a saved version I can distribute.

Thanks again for sticking with me.

[arlu1201]

One clarification - When the macro runs, the lockdown results might not always be the sheet that is visible to you. Have you clicked onto that tab to see if its the results are showing after running the macro?

Updated file attached.

[PFDave]

Thanks Arlu, however when I copy a different volume of data, or put new data into the first tab I get another error which relates too

Please Login or Register  to view this content.

[arlu1201]

Into which tab are you copying the data? What is the error?

[PFDave]

The error message says - The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following - Click a single cell, and then paste - Select a rectangle that's the same size and shape, and then paste

this is into the insert data tab

[PFDave]

I've also noticed in the calculator sheet it is looking for your documents in the vlookup

[arlu1201]

The error message says - The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following - Click a single cell, and then paste - Select a rectangle that's the same size and shape, and then paste

It has worked at my end. Try this updated code

Please Login or Register  to view this content.

I've also noticed in the calculator sheet it is looking for your documents in the vlookup

Yes, since you are using multiple versions of your file, you need to change the formulae. You can go to Edit Links and change it to your file. Or change it once in the top line. The macro will do the autofill for you.

[PFDave]

YES!!!! it works now!!! You are a genius!!! Thank you so much for this mate!!!!