Copy properties from previous row to new row

[ammdumas]

OK. I'm starting to figure out For Next Loops but still having problems.

I want to be able to find the first blank row in a spreadsheet then copy the properties of the previous row into that new row (i.e. formulas, validation ,etc.). I started with this (just picking any source row) then realized that that would do every row in the spreadsheet!

For MY_ROWs = 6 To Range("A65536").End(xlUp).row
If Range("A" & MY_ROWs).Value = " " Then
Rows("6:6").Select
Selection.Copy
MY_ROWs.Select
ActiveSheet.Paste
Range("A" & MY_ROWs).Select
Next MY_ROWs
End Sub

As an added bonus the first column has an incremental number which is cell C3+ a number (i.e. If C3 = 1501-421 then Column A is 1501-421-1, 1501-421-2, 1501-421-3, etc.)

I'd be happy just to get the first part done then I could add a prompt telling the user to add the new number. But any help is appreciated.

 

[Tom Urtis]

One way, given limitation on copy and pasting non-contiguous ranges:

Sub Test2()
Dim x As Long, y As Long
x = Cells(Rows.Count, 2).End(-4162).Row
y = x + 1
Range("A" & x & ":B" & x).Copy Range("A" & y)
Range("D" & x & ":F" & x).Copy Range("D" & y)
Range("H" & x & ":J" & x).Copy Range("H" & y)
Range("L" & x & ":IV" & x).Copy Range("L" & y)
End Sub

 

[Corleone]

Perfect

Thanks

 

[ammdumas]

Hey Tom,

Got a tougher one for you (BTW, your solution worked. Thx). How do insert the row as opposed to placing it at the end. In column C there are a bunch of project numbers (i.e.1501-001, 1501-002, 1501-004, etc.). Assume I create and input box to type in the project number of 1501-003, how could I get the macros to insert the row, same rules as before, but look at the project number and insert the row in the appropriate order?

 

[Tom Urtis]

Try this:

Sub Test3()
Dim x As Variant
Set x = Columns(1).Find(What:=InputBox("Please enter the project number for row insertion", "What project number should be copied?", "####-##"), LookIn:=xlFormulas, LookAt:=xlPart)
If x Is Nothing Then
MsgBox "You entered a value that does not exist." & vbCrLf & "Please click OK to exit.", 48, "No such project number."
Exit Sub
ElseIf Len(x) = 0 Then
MsgBox "You clicked Cancel or entered nothing" & vbCrLf & "Please click OK to exit.", 64, "Action cancelled."
Else
Rows(x.Row + 1).Insert
Dim z As Long
z = x.Row + 1
Rows(z - 1).Copy Rows(z)
Cells(z, 1) = Cells(3, 3) & " - " & z - 5
Range(Cells(z, 7), Cells(z, 8)) = "OPEN"
Range(Cells(z, 9), Cells(z, 14)) = "NO"
End If
End Sub

 

[ammdumas]

Doesn;t quite work the way I wanted. Your code searches for an existing project number, and if it finds it, places a new row under the existing one with the same project number. I want it to find a gap and insert the row in the appropriate place.

I.e. 1501-001, 1501-002, 1501-004 exist (1501-003 missing)

User entered 1501-003. Find one before (i.e. 1501-002) and insert row under.

If user types one that already exists, then macros cancels out (existing code you wrote takes care of that).

Is this clearer? I appreciate the help. ray:

 

[Tom Urtis]

No, not clearer. What did this mean? - -
"Assume I create and input box to type in the project number of 1501-003"

If you want to search for blank cells, why not search for blank cells, instead of possible existing project numbers to see if maybe they abut an empty cell. Your approach does not make sense as you convey it. Also then, why do it one at a time? Why not just run one macro to look for all the empty cells and fill in those rows? I don't understand what you need, or why you are taking the approach to do what you need. It could definitely be me though...I'm trying to complete 2 large projects this weekend and my brain cells are scattered in different directions.

FYI, I'll be away from the board for part of the day, so anyone reading this that sees it differently than I do is certainly welcome to chime in.

 

[ammdumas]

You know, I talked to the user and she's just gonna sort the **** thing. I still use the code to create a new row, but she can then sort at a later date. Thanks for the help anyways.