copy a row down x times

[Mocano]

Hi Guys,

I am looking for someone to help me please.

I have a table like this one below. All cells are formula base and it is starting at A10. Column will vary to up to 100

Row number----Customer----Invoice Date
140----------------cpy a-----30/05/2012
189--------------- cpy a-----20/12/2012
217--------------- cpy a-----22/10/2010

Row calculation is below. The other columns are just simple vlookup to fetch the data I need link to the row number given.
{=IFERROR(INDEX(SASUSD, SMALL(IF((INDEX(SASUSD, , $D$5, 1)<=$D$4)*(INDEX(SASUSD, , $D$5, 1)>=$D$3), ROW(SASUSD)-MIN(ROW(SASUSD))+1, ""), ROWS('database reporting'!A19:$A$19)), , 1)," ")}

I have also a cell in F3 a countif formula that will give me the number of row I need to be copied down from A11 in the table above. I want to keep row 10 always alive in the xls.

What I am looking for is

1- delete all rows down from A11 (this is to clean spreadsheet)
2- copy/paste row 10 the number of times cell F3 says for eg: 25
3 - that is an extra add a sum function at the end of column O and P.

I will add the macro to a button so that when I click it refresh the data.

Any help will greatly appreciated as I am useless at writing macro.

Cheers

[arlu1201]

Do you have a sample file which you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Mocano]

SAS USD online.xlsm

Hi Arlu,

I have attached a file for you to see what I am looking for.

Thanks for taking an interest in my post.

Cheers

[AB33]

Your requirement is not clear. You have formulas in A11 all the way down, so when you copy the F4 value(Not F3 by the way), do you want to copy to the same cells which already have formulas, or do you want to copy it as values only? I have assumed you want to copy values only, but this can easily change. I am also not sure if you just want to copy row 10 only, but this can easily be changed.
This code deals with part 1 and 2 of your question and some one should be able to help with the sum function.

Please Login or Register  to view this content.

[Mocano]

Hi,

My apology for the confusion, I have had to redo my spreadsheet before sending it andi must i have not put the data correctly.

Many thanks for point -1
for point 2. Row 10 is the master line that will always remain in the spreadsheet. Cell F5 (not 3 sorry) is the number of time the row 10 has to be copied under row 10 and this number will vary depending on my criteria selection. It is not a value paste I need but simple copy paste (with everything) so that the formula on each line can search the database.

At the moment because my selection of criteria can bring between 1 to 500 hit (lines that will need in the table) this means that I have to have a table with a minimum of 500 lines of formula to be sure that I am not missing any data which make the spreadsheet heavy.
But if possible, by adding a macro the excel spreadsheet will know that for example in F5, I have a number 5 showing then the the row 10 will be copied 5 times for row 11 to 15. And if my selection say in cell F5 : 250 the row 10 will be copied 250 times from row 11 to 260.

I hope that I have been clearer in my comment. It is harder that I thought to explain in word what I need excel to do

cheers

[AB33]

Still F4 on the attached.
I have amended the code. It means despite row 10 is copied 5 times, the value copied are not the same as row 10, but the value of the formulas. Try it and see that is what you wanted.

[Mocano]

That is super fast thank you.

I have tried it and it is working like a charm, thank you so much.

Can I just ask for very slight modification on point 1, instead of clear row can you change it to delete row. It will make the spreadsheet a lot clearer.
thanks

[AB33]

Change this line of the code

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

[Mocano]

THANK YOU for your patience and help. Everything is working perfectly fine.

Case Solved

[AB33]

Mocano,
You are welcome!