Macro Help Please
 

Hello Group
I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like
to copy the data in the first row down and fill in the blanks where A2:F4
=A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc
How do I do this?
Thanks!

Macro Help Please
 

If this is a one-time need, I'd just create the formulas to do it...
Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data
Filter Autofilter) and select 'blanks' in the first dropdown. Since it's
blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that
formula across to column F and then down to all the visible rows. Turn off
your filter and all the blank rows should be there.
If you need to 'lock down' the values (getting rid of the formulas we just
entered), select the entire table and Copy / Paste Special values.

"Al" wrote:

Hello Group
I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like
to copy the data in the first row down and fill in the blanks where A2:F4
=A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc
How do I do this?
Thanks!

Macro Help Please
 

Thanks for the response. Not a one time deal. I am trying to add to
existing code that is run in a regular basis. Thanks anyway!

"bpeltzer" wrote:

If this is a one-time need, I'd just create the formulas to do it...
Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data
Filter Autofilter) and select 'blanks' in the first dropdown. Since it's
blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that
formula across to column F and then down to all the visible rows. Turn off
your filter and all the blank rows should be there.
If you need to 'lock down' the values (getting rid of the formulas we just
entered), select the entire table and Copy / Paste Special values.

"Al" wrote:

Hello Group
I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like
to copy the data in the first row down and fill in the blanks where A2:F4
=A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc
How do I do this?
Thanks!

Macro Help Please
 

Ok, then VBA it is...
Dim sourcerow
For sourcerow = 1 To 9 Step 4 'fill in the appropriate start and end row#s
Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy
Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial _
Paste:=xlPasteValues
Next

"Al" wrote:

Thanks for the response. Not a one time deal. I am trying to add to
existing code that is run in a regular basis. Thanks anyway!

"bpeltzer" wrote:

If this is a one-time need, I'd just create the formulas to do it...
Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data
Filter Autofilter) and select 'blanks' in the first dropdown. Since it's
blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that
formula across to column F and then down to all the visible rows. Turn off
your filter and all the blank rows should be there.
If you need to 'lock down' the values (getting rid of the formulas we just
entered), select the entire table and Copy / Paste Special values.

"Al" wrote:

Hello Group
I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like
to copy the data in the first row down and fill in the blanks where A2:F4
=A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc
How do I do this?
Thanks!

Macro Help Please
 

OK But row 9 is dynamic how do I incorporate last row?
Thanks

"bpeltzer" wrote:

Ok, then VBA it is...
Dim sourcerow
For sourcerow = 1 To 9 Step 4 'fill in the appropriate start and end row#s
Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy
Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial _
Paste:=xlPasteValues
Next

"Al" wrote:

Thanks for the response. Not a one time deal. I am trying to add to
existing code that is run in a regular basis. Thanks anyway!

"bpeltzer" wrote:

If this is a one-time need, I'd just create the formulas to do it...
Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data
Filter Autofilter) and select 'blanks' in the first dropdown. Since it's
blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that
formula across to column F and then down to all the visible rows. Turn off
your filter and all the blank rows should be there.
If you need to 'lock down' the values (getting rid of the formulas we just
entered), select the entire table and Copy / Paste Special values.

"Al" wrote:

Hello Group
I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like
to copy the data in the first row down and fill in the blanks where A2:F4
=A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc
How do I do this?
Thanks!

Macro Help Please
 

Whatever your variables are, you can just change the conditions on the For.
Alternately, you could create something like
Dim sourcerow
sourcerow = 1
While Cells(sourcerow, 1) < ""
Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy
Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial
Paste:=xlPasteValues
sourcerow = sourcerow + 4
Wend
This just continues until it finds column A blank in the next sourcerow.


"Al" wrote:

OK But row 9 is dynamic how do I incorporate last row?
Thanks

"bpeltzer" wrote:

Ok, then VBA it is...
Dim sourcerow
For sourcerow = 1 To 9 Step 4 'fill in the appropriate start and end row#s
Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy
Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial _
Paste:=xlPasteValues
Next

"Al" wrote:

Thanks for the response. Not a one time deal. I am trying to add to
existing code that is run in a regular basis. Thanks anyway!

"bpeltzer" wrote:

If this is a one-time need, I'd just create the formulas to do it...
Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data
Filter Autofilter) and select 'blanks' in the first dropdown. Since it's
blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that
formula across to column F and then down to all the visible rows. Turn off
your filter and all the blank rows should be there.
If you need to 'lock down' the values (getting rid of the formulas we just
entered), select the entire table and Copy / Paste Special values.

"Al" wrote:

Hello Group
I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like
to copy the data in the first row down and fill in the blanks where A2:F4
=A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc
How do I do this?
Thanks!