Thread: Macro for copy/insert into expanding table

Hey guys,

First time poster here so please bare with me!

I have a table that gets built out of about 100 different scorces. they all have the same format, what i need help with building is a macro that will copy and insert the info from the top equaly formatted area into the growing table below and be able to repeat the function to the same areas of the table as it grows.

I can record a macro that will work once but once the destination cells have been shiffted by the second use of the macro it all goes down hill. How do you get the macro to copy and insert correctly?

Here is the macro that i have recorded and works just once since the rows referanced are shifting as I copy and paste it.

Code:

Sub copy_insert_test()
'
' copy_insert_test Macro
' Macro recorded 1/28/2008 by z18v
'

'
    Rows("5:6").Select
    Range("H5").Activate
    Selection.Copy
    Rows("25:25").Select
    Range("H25").Activate
    Selection.Insert Shift:=xlDown
    Rows("7:8").Select
    Range("H7").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Rows("31:31").Select
    Range("H31").Activate
    Selection.Insert Shift:=xlDown
    Rows("9:10").Select
    Range("H9").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Rows("37:37").Select
    Range("H37").Activate
    Selection.Insert Shift:=xlDown
    Rows("11:12").Select
    Range("H11").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Rows("43:43").Select
    Range("H43").Activate
    Selection.Insert Shift:=xlDown
    Rows("13:14").Select
    Range("H13").Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=6
    Rows("49:49").Select
    Range("H49").Activate
    Selection.Insert Shift:=xlDown
    Rows("15:16").Select
    Range("H15").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Rows("55:55").Select
    Range("H55").Activate
    Selection.Insert Shift:=xlDown
    Rows("17:18").Select
    Range("H17").Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=6
    Rows("61:61").Select
    Range("H61").Activate
    Selection.Insert Shift:=xlDown
    Rows("19:20").Select
    Range("H19").Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=6
    Rows("67:67").Select
    Range("H67").Activate
    Selection.Insert Shift:=xlDown
    ActiveWindow.SmallScroll Down:=-3
    Rows("21:22").Select
    Range("H21").Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=12
    Rows("73:73").Select
    Range("H73").Activate
    Selection.Insert Shift:=xlDown
End Sub

I know the reason that it doesn't work is that the macro refrences the cells that have shifted on the second inserting and starts inserting the copyed rows in the wrong locations. How do you referance a shifting area?

I would like to attach the workbook the visual might help.... whats the best way to attach the workbook?

Thanks everyone!

Yes it probably would help. You need to zip the file first.

so maybe the real question is can excel even do this?? Anyone...???

I don't think this is a very good way of using Excel. You have the information at the top and then you want to copy it to rows underneath? I don't follow - what are you trying to achieve?

thanks for the reply stephenr.

you said it perfectly, I want to copy the info from the oder summary at the top into the table down below to the corresponding class. The macro that i have recored does the copy and insert once correctly but it cant keep repeating with out mixing the different classes, since it is building the table and the cells shift.

Is there another way to accomplish this same task that I don't know about?

If you run the macro it works great the first time. I just need it to be able repate the same process over and over....

I will attach a copy of the workbook with an example so you can see the operation working the first time, then what happens if you run the macro again. I need to get to my comp at home to attach it but thank you again for looking at it!

This is an easy way to duplicate any row in your table. it basically copies and pastes the entire row to a row directly below it. I have not been able to successfully be able to insert in the bottom of your sheet but it's a quick start

Code:

Sub InsertCopyRow()
    ActiveCell.EntireRow.Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

Just wondering, would it be possible to Un-Merge all those cells and format your data differently?

Yes but why do you need to reproduce the data - it seems like a duplication of effort? I'll have a look tomorrow if ashley or somebody else hasn't solved it in the meantime.

alright, so just so i make 100% clear you have rows 5-22 where your main data goes. after you put the data in, you want to copy the row and put them in the table below, the oldest data on the bottom, grouped with correct class, then clear rows 5-22 so that you can put all new data in??? then repeat the process again?

OK I think I worked it out for you. Try this. The "bottom table" data has to exist for it to work, so you would have to run your macro first because mine depends on that lower data being there. Also this macro is only going to work if that is all the fields that you have in the "upper table". I changed AC5 to have a sum if you don't already have that.

Let me know if this is what you were looking for. It's probably not the best way to do it but I'm out of ideas.

B,

Not a problem, glad I could help you.

From start to finish - the Application.ScreenUpdating = False tells you basically not to flash the screen as you run through the report. Then you select the first cell, then the entire row and copy it. The (') before after means a comment. thats just notes for you. What I was trying to say is that the cell following after should be your first empty row after the upper sheet. in this case it was. if you ever add rows you will have to modify this.

The rest of that command basically is like the find next command that you can do yourself manually. Then once it finds the next cell it inserts the cells you copied above where it finds it.

Hope that makes sense. I am still learning myself. I play around a lot with the record function to see how excel does thing then I research and then when I get stuck I ask questions. Hope you were able to get what you wanted to do finished.

Code:

Application.ScreenUpdating = False

    Range("a5").Select
    ActiveCell.EntireRow.Select
    Selection.Copy
    'After value must be the first blank row separating your main window from the lower.
    Cells.Find(What:="10 L/S Woven", After:=[a23], LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False

Hey Ashley,

Do you think there would be away to throw an "if AC5 = 0 then skip to next class" in the code? I am trying to make it so that if the row doesn't have any value in it then the macro wont cut and paste it down below in the table. I have been messing with it the last couple of days and I haven't been able to get the logic correct yet. Thanks again for all your help you have been great!

Bud