Inserting A selection before another

**Link Strife** · 01-11-2005, 06:55 PM

The task that I wish to complete is not a simple one for me, maybe I can ask one of you veterans how to do it.

My problem is this, I have a group of cells, say A1 to D5. I need to take this selection and paste them into a new area near the bottom of the sheet before a "Summary" set of rows. So to attempt to show you what I need to do.
____A_______B_________C__________D_______
1 |Project #|Priority #|People involved|Hours worked|
2 |________|_______| Name_______|___ 7 ______|
3 |________|______ | Name _______|___ 4 _____|
4 |________|______ | Name _______| ___ 3 _____ |
5 |________|_______| Name_______|____ 7_____ |
6 Need to take selection and "copy and insert" here
7 ____________________________________________
8 |________|________|___Summary__| Total hours ___|
9 |________|________|Name________| hours________|
10 |________|________|Name________| hours________|
11 |________|________|Name________| hours________|
12 |________|________|Name________| hours________|

Just normally copying is easy, inserting at the bottom before the Summary is easy, that is if you do it manually.

I am attempting to set up a macro that will check the columns and insert it automatically. I was thinking of either checking the "C" column and inserting the selection right after it or searching the sheet for the word "Summary" and inserting the selection right above it. I just don't know how to set up the macro to do it for me.

An entirely seperate problem that I will be having next is, how do I get the "total hours" column to update to include the hours of the newly inserted rows?

Any help is apprieciated and please excuse my horrible art

Link S

**anilsolipuram** · 01-11-2005, 07:34 PM

below code is searching for "summary" and inserts copied cells of "a1:d5" next row after it finds "summary" text.

Sub macro()
On Error GoTo a:
Dim k As Integer
k = 1
Cells.Select
Selection.Find(What:="summary", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
k = 2
a:

If k = 1 Then
Range("a1").Select
MsgBox "no summary found"
Else
i = ActiveCell.Row
Range("a1:d5").Select
Selection.Copy
Range("a" & (i + 1)).Select
ActiveSheet.Paste
End If
End Sub

I guess total hours can be calculated by selecting the cell, where you want the result and using the code, activecell.formula='"=sum("d2":d" & lastrow)" , last row can be calculated using code
range("d2").select
Selection.End(xlDown).Select
lastrow=ActiveCell.Row

**Link Strife** · 01-12-2005, 10:47 AM

Thanks I will use this right away.

Link S

**Link Strife** · 01-12-2005, 12:13 PM

Ok thanks to what anilsolipuram gave me yesterday I managed to get this far, had to modify it a bit to make it work correctly.
Notice at the bottom I am using most of his formula to help me get this far, but now I need to know. Is there an easier way to clear cell contents? As you can see at the bottom I have K, L, M and N clearing, but I still need to go all the way to Column AL, and instead of having a couple pages worth of cell clearing I was wondering if there was a way to clear K to AL easily?
Sub InsertRows()

' InsertRows Macro
' Macro recorded 1/11/2005 by darnel_gobeil
On Error GoTo a:
Dim k As Integer 'setting up k, k is nothing more then a true/false check
k = 1 'our test number
Cells.Select 'must study more to figure out exactly what this means
Selection.Find(What:="summary", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
k = 2 'but once its done searching for the word "summary" it changes k to 2
a:

If k = 1 Then 'so if summary is NOT found then k is still equal to 1
'and when its tested and executes the If part
Range("a1").Select 'highlights a1
MsgBox "no summary found" 'lets the user know that "summary" was not found
Else 'if "summary" was found then k is equal to 2
i = ActiveCell.Row
Range("a6:aj12").Select 'selects the top project
Selection.Copy 'copies it
Range("a" & (i - 1)).Select 'goes to where summary is, and moves 1 above it
Selection.Insert Shift:=xlDown 'puts the top project in
'then the user is asked for the Projects Number, Priority number and name
Range("a" & (i - 1)).Value = InputBox("What is the Project Number?")
Range("b" & (i - 1)).Value = InputBox("What is the Priority Number?")
Range("c" & (i - 1)).Value = InputBox("What is the Projects Name?")
End If 'clearing the contents of k
Range("k" & (i + 0)).ClearContents
Range("k" & (i + 1)).ClearContents
Range("k" & (i + 2)).ClearContents
Range("k" & (i + 3)).ClearContents
Range("k" & (i + 4)).ClearContents
Range("k" & (i + 5)).ClearContents
'clearing the contents of l
Range("l" & (i + 0)).ClearContents
Range("l" & (i + 1)).ClearContents
Range("l" & (i + 2)).ClearContents
Range("l" & (i + 3)).ClearContents
Range("l" & (i + 4)).ClearContents
Range("l" & (i + 5)).ClearContents
'clearing the contents of m
Range("m" & (i + 0)).ClearContents
Range("m" & (i + 1)).ClearContents
Range("m" & (i + 2)).ClearContents
Range("m" & (i + 3)).ClearContents
Range("m" & (i + 4)).ClearContents
Range("m" & (i + 5)).ClearContents
'clearing the contents of n
Range("n" & (i + 0)).ClearContents
Range("n" & (i + 1)).ClearContents
Range("n" & (i + 2)).ClearContents
Range("n" & (i + 3)).ClearContents
Range("n" & (i + 4)).ClearContents
Range("n" & (i + 5)).ClearContents

End Sub