PDA

View Full Version : [SOLVED] Copy Current Row and paste to the next couple hundred rows



Ike
04-23-2015, 05:56 AM
Hi,


I have a worksheet that has data validation and formulas in several cells across a row and in every row down to row 500. As rows with data above row 500 are regularly deleted (not cleared), then row 500 no longer has the data validation and formula cells.


I would like a macro, that I can add to a pre-existing code, that would find the first empty row of column B. Column B does not contain any data validation or formulas.

Copy the entire Row with data validation and formulas
Paste to all rows down to a maximum of row 500. I would like to be able to change row 500 to any other number of rows if needed. Say, next week I need to change it to 600.




Thank you in advance. :)


Alvin

snb
04-23-2015, 06:10 AM
Checkout: .filldown

Ike
04-23-2015, 07:22 AM
I searched for .filldown and all I find is using a specified range. Which I understand.
I am simply trying to set the sheet up for the day.

What I am having trouble with is copying the selected row (which is varible) and then pasting the data from that row to all the rows down to row 500.

The following selects and copies the first row and pastes to rows 400 through 500. For me that is a simple workaround, however if data is auto populated during the day that would exceed row 400 then of course the following code would not work the next day, as it would overwrite rows 400 through whatever.



Dim FirstBlankCell As Range
Set FirstBlankCell = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Select

Selection.EntireRow.Select
Selection.Copy


Rows("400:500").Select 'I would like to paste from the first row selected instead of setting a specific range.
ActiveSheet.Paste

How do I paste from the first row found to row 500?

Thanks again for your assistance.