Macro Question

[les]

I am looking for code to do the following.
I want to insert a new line at the end of (Range_1). I then want to take a
record from (Range_2) and paste value that in the line I just inserted.
I am trying to set this up as a repeatative process.

The code that I am using just copys the range and paste it at the end of the
last record. This is almost what I want but not exact.

Dim RngToCopy As Range
Dim NextCell As Range

With Worksheets("Database")
Set NextCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With Worksheets("Database")
Set RngToCopy = .Range("NewRecord")
End With

RngToCopy.Copy _
Destination:=NextCell

End Sub


Thanks!

 

[Don Guillett]

Simple macro recording that should then be cleaned up
Sub Macro9()
'
' Macro9 Macro
' Macro recorded 6/9/2008 by Donald B. Guillett
'

'
Selection.Cut
Sheets("Sheet3").Select
Range("I8").Select
Selection.Insert Shift:=xlDown
End Sub

 

[les]

Don,
Would you mind if I sent you the spread sheet?
Maybe that would explain what I am trying to do.

 

[Rick Rothstein \(MVP - VB\)]

If everything you posted is not doing what you want, can you post the one
thing you omitted from your message... exactly what it is not doing that you
actually want it to do? In other words, in what way is it "almost what [you]
want but not exact"?

Rick

 

[Don Guillett]

OK, send to my address below but you must include before/after examples and
your code.

 

[Don Guillett]

I sent OP this where newrecord is a defined name for a moving row

Sub InsertNewRecord()
destrow = Columns("b").Find("Total").Row
Rows(destrow).Insert
'line in highlited so use this to copy
Rows(destrow - 1).Value = Range("newrecord").Value
Range("newrecord").Cells(1, 1).Select
End Sub

You can even put this in the Sheet module and double click anywhere on the
new record line
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Range("newrecord")) Is Nothing Then Exit Sub
Call InsertNewRecord
End Sub