Insert Rows and Update Formulas

[MOXY1975]

I have a table ~100 rows long and 206 columns wide.

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K.....HA
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Cust# 1
[/TD]
[TD]Gen Type
[/TD]
[TD]Demand Profile
[/TD]
[TD]Max Contract Qty
[/TD]
[TD]Blank
[/TD]
[TD]Label
[/TD]
[TD][/TD]
[TD]Lookup formula
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Cust #2
[/TD]
[TD]Gen Type
[/TD]
[TD]Demand Profile
[/TD]
[TD]Max Contract Qty
[/TD]
[TD]Blank
[/TD]
[TD]Label
[/TD]
[TD][/TD]
[TD]Lookup formula
[/TD]
[/TR]
[TR]
[TD]3......
[/TD]
[TD]Cust #3
[/TD]
[TD]Gen Type
[/TD]
[TD]Demand Profile
[/TD]
[TD]Max Contract Qty
[/TD]
[TD]Blank
[/TD]
[TD]Label
[/TD]
[TD][/TD]
[TD]Lookup formula
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]Cust #100
[/TD]
[TD]Gen Type
[/TD]
[TD]Demand Profile
[/TD]
[TD]Daily Contract Qty
[/TD]
[TD]Blank
[/TD]
[TD]Label
[/TD]
[TD][/TD]
[TD]Lookup formula
[/TD]
[/TR]
</TBODY>[/TABLE]

Now I need to split each customer' day into 24 hours (involving adding 25 rows (24 hours + a total) between each customer. I also need to move the data currently in $I$1:$HA$1 to $I$2:$HA$$2 but only after I have inserted the rows. If I do this manually it is going to take forever. Is there a way of doing it efficiently with VBA?

Below is the recorded macro of my manual actions together with narrative of each action
' rowinsertxple Macro
'
'Step 1: Insert 27 new rows beneath the first row of my table
Range("D594:D620").Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'This moves the data in the first row J - HA down 1 row
Range("J593").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut Destination:=Range("J594:HA594")
'This copies a defined range of formulas into newly created rows
Range("J594:HA594").Select
Application.Goto Reference:="Copy_Paste"
Selection.Copy
Range("D595").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'This copies a defined range of formulas into what was formerly the first row (column j-h) of the table
Application.Goto Reference:="Season"
Selection.Copy
Range("J593").Select
ActiveSheet.Paste

End Sub

I need to repeat this 100 times.......

Is there shorter way of doing this?

Thanks in advance

 

[Jerry Sullivan]

Hi MOXY1975,

What you describe looks like a good application for MS Query within Excel. Have you used that before or used SQL?

One approach would be to make a table that has the times by hour from 0:00 to 23:00 then use a query to join the combinations of customer data and times by hour. This will quickly generate the 2400 rows of data and more importantly when you add another 10 customers, you would just add those to the customer data table and refresh your query to get the additional 240 rows added.

You could then either fill columns K:HA of the resulting table with your lookup formulas; or possibly do the lookups through the same query.

Rather than putting in an extra row for a subtotal for each Customer, a cleaner solution would be to make the joined table described above, then use a PivotTable to summarize and present the data including subtotals.

I'd be glad to help with more detail if you want to pursue that approach.