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.

 

[onlyadrafter]

Hello,

This code will find the first blank row at the end of the spreadsheet and copy the line above to it. Does your spreadsheet have gaps in between the first row and last row?

Sub Macro4()
    Rows(Range("A65536").End(xlUp).Row).Select
    Selection.Copy
    Range("A65536").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Is the first column number sequential i.e. as you say C3 is 1501-421, so is A3 1501-421-1, A4 is 1501-421-2 so A100 would be 1501-421-98?

If so add this line of code before the End Sub

    Range("a65536").End(xlUp).Value = Range("C3").Value & "-" & Range("a65536").End(xlUp).Row - 2

Is this working OK for you?

 

[ammdumas]

Hey onlyadrafter (like me kinda),

Everything worked. I just had to adjust that last little bit from '-2' to '-5' because the numbers start in A6 (and ya, there are no spaces). So thanks once again.

Austin.

 

[ammdumas]

Appealing to onlyadrafter, but I'll except any help from anybody.

I modified code given to me from onlyadrafter to copy stuff to the next available row. In this case I'm also adding default values to the new row...

Rows(Range("A65536").End(xlUp).Row).Select
Selection.Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("a65536").End(xlUp).Value = Range("a65536").End(xlUp).Row - 6
Range("g65536").End(xlUp).Value = "OPEN"
Range("h65536").End(xlUp).Value = "OPEN"
Range("i65536").End(xlUp).Value = "NO"
Range("j65536").End(xlUp).Value = "NO"
Range("k65536").End(xlUp).Value = "NO"
Range("l65536").End(xlUp).Value = "NO"
Range("m65536").End(xlUp).Value = "NO"
Range("n65536").End(xlUp).Value = "NO"

Thing is, this is only good if their is actual data in each column of the previous row. For example, if columns G and H are blank in the last row, then this code changes the value of the first row it finds with data in it (i.e. if there a several rows of data but columns G and H are blank, then it changes the header to 'OPEN' ). How do I fix that?

 

[Tom Urtis]

See if this does what you want:

Sub Test1()
Dim x As Long
x = Cells(Rows.Count, 1).End(-4162).Row + 1
Rows(x - 1).Copy Rows(x)
Cells(x, 1) = Cells(3, 3) & " - " & x - 5
Range(Cells(x, 7), Cells(x, 8)) = "OPEN"
Range(Cells(x, 9), Cells(x, 14)) = "NO"
End Sub

 

[Corleone]

Hi
I,ve used part of the code above just to simply copy a row of text & formulas down This works great - how can I modify this so that it ignores the cells in columns A,G & J - The modified code I am currently suing is below
Thanks

Sub Test1()
Dim x As Long
x = Cells(Rows.Count, 1).End(-4162).Row + 1
Rows(x - 1).Copy Rows(x)


End Sub

 

[Tom Urtis]

The modified code I am currently suing is below

Well gee, I hope my code wasn't so offensive I'm being sued for posting it !!

Can you please clarify what column, since it's not A in your case, defines what the last row is, and hence what the next row should be?

Please also clarify what you mean by wanting to ignore cells in A, G, and J. The more specific you are, the better we can understand what you are needing to have solved.

 

[Corleone]

Hi
The Spreadsheet(s) I am working on goes from
row 5 down to row 52

It contains weekly forecasts

It currently has entries populating rows 5 down to 23

when I run the macro to copy down into the next row, I want the macro
to ignore anything in the cells "C" , "G" + "K" from the row it is copying from

Cheers

 

[Tom Urtis]

Well, I asked you a question you did not answer, so I'll try again. What column is it that is always populated and, by virtue of it being empty, defines "the next row" of the spreadsheet?

Now, two more questions:

What columns exactly do you want to ignore? In your first post you said A, G, and J. In your last post you said C, G, and K. Please clarify.

What is the last column of your range, or do you just want the entire row copied (out to IV), except for the mystery 3 or so columns?

 

[Corleone]

i) column b is always populated

ii)I need to ignore C , G & K

iii) would like the entire row copied

Thanks