Copy properties from previous row to new row

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469
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! :eek2:

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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?

Code:
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

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

Is this working OK for you?
 
Upvote 0
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. :rofl:

Austin.
 
Upvote 0
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' :banghead: ). How do I fix that?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Corleone said:
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
i) column b is always populated

ii)I need to ignore C , G & K

iii) would like the entire row copied

Thanks
 
Upvote 0

Forum statistics

Threads
1,224,315
Messages
6,177,845
Members
452,809
Latest member
mar_luna

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top