I am trying to copy one row with formula in it(C7:AI7), to the rows below it,
the number of rows to be copied down the sheet depends on the number of
filled cells in col B (max B7:B67). I.e. If there are four filled cells in
col B (7-11) then I want the range C7:AI7 to be copied down to match. Now
desperate. Anyone help please ?

Thanks in hope

Sybs

Sub FillFormulas()
Dim rng As Range
Set rng = Range("B7", _
Cells(7, "B").End(xldown))
rng.Offset(0, 1).Resize(, 33).FillDown
End Sub

--
Regards,
Tom Ogilvy

"SYBS" wrote:

I am trying to copy one row with formula in it(C7:AI7), to the rows below it,
the number of rows to be copied down the sheet depends on the number of
filled cells in col B (max B7:B67). I.e. If there are four filled cells in
col B (7-11) then I want the range C7:AI7 to be copied down to match. Now
desperate. Anyone help please ?

Thanks in hope

Sybs

Hi Tom,

That works for me !!
I di try offset but thought it needed to be in a loop. Can you please
explain how the Resize(, 33).FillDown works and what is the significance
of the '33'.

For future reference, could I put the offset bit into a For each loop if
needed or is the offset more efficient.

Last question. If I want to put a name from a list into every other 6th cell
in a column, how do I phrase that ?

Thanks again, really appreciated

Sybs

"Tom Ogilvy" wrote:

Sub FillFormulas()
Dim rng As Range
Set rng = Range("B7", _
Cells(7, "B").End(xldown))
rng.Offset(0, 1).Resize(, 33).FillDown
End Sub

--
Regards,
Tom Ogilvy

"SYBS" wrote:

I am trying to copy one row with formula in it(C7:AI7), to the rows below it,
the number of rows to be copied down the sheet depends on the number of
filled cells in col B (max B7:B67). I.e. If there are four filled cells in
col B (7-11) then I want the range C7:AI7 to be copied down to match. Now
desperate. Anyone help please ?

Thanks in hope

Sybs

There are 33 columns in the range C:AI, so the 33 extends the range to
include these columns.

There is no reason to loop for the problem described unless you want the
code to run slower and be more inefficient.

What does every other 6th cell mean? Do you mean every 12th cell?

--
Regards,
Tom Ogilvy


"SYBS" wrote:

Hi Tom,

That works for me !!
I di try offset but thought it needed to be in a loop. Can you please
explain how the Resize(, 33).FillDown works and what is the significance
of the '33'.

For future reference, could I put the offset bit into a For each loop if
needed or is the offset more efficient.

Last question. If I want to put a name from a list into every other 6th cell
in a column, how do I phrase that ?

Thanks again, really appreciated

Sybs

"Tom Ogilvy" wrote:

Sub FillFormulas()
Dim rng As Range
Set rng = Range("B7", _
Cells(7, "B").End(xldown))
rng.Offset(0, 1).Resize(, 33).FillDown
End Sub

--
Regards,
Tom Ogilvy

"SYBS" wrote:

I am trying to copy one row with formula in it(C7:AI7), to the rows below it,
the number of rows to be copied down the sheet depends on the number of
filled cells in col B (max B7:B67). I.e. If there are four filled cells in
col B (7-11) then I want the range C7:AI7 to be copied down to match. Now
desperate. Anyone help please ?

Thanks in hope

Sybs

Thanks for that, so I could use that number to represent different column
ranges, that will make a difference.

The 6/12th row explanation is. From my list of names on Sheet 1 I am
populating col B on 5 other sheets, 1 row to a name, but two other sheets use
6 rows per name, so the 1st name would be on the 13th line, 2nd name on 20th
line etc. At the moment I have simply coded the On activate for the list to
be copied and pasted to the other pages (where they take up one row). So I
am looking to put the names on the other sheets in Col B13/b20/b27 etc.

"Tom Ogilvy" wrote:

There are 33 columns in the range C:AI, so the 33 extends the range to
include these columns.

There is no reason to loop for the problem described unless you want the
code to run slower and be more inefficient.

What does every other 6th cell mean? Do you mean every 12th cell?

--
Regards,
Tom Ogilvy


"SYBS" wrote:

Hi Tom,

That works for me !!
I di try offset but thought it needed to be in a loop. Can you please
explain how the Resize(, 33).FillDown works and what is the significance
of the '33'.

For future reference, could I put the offset bit into a For each loop if
needed or is the offset more efficient.

Last question. If I want to put a name from a list into every other 6th cell
in a column, how do I phrase that ?

Thanks again, really appreciated

Sybs

"Tom Ogilvy" wrote:

Sub FillFormulas()
Dim rng As Range
Set rng = Range("B7", _
Cells(7, "B").End(xldown))
rng.Offset(0, 1).Resize(, 33).FillDown
End Sub

--
Regards,
Tom Ogilvy

"SYBS" wrote:

I am trying to copy one row with formula in it(C7:AI7), to the rows below it,
the number of rows to be copied down the sheet depends on the number of
filled cells in col B (max B7:B67). I.e. If there are four filled cells in
col B (7-11) then I want the range C7:AI7 to be copied down to match. Now
desperate. Anyone help please ?

Thanks in hope

Sybs

Dim list as Range, cell as range
Dim rw as long
rw = 13
set list = Worksheets("Sheet1").Range("B1:B20")
for each cell in list
worksheets("Sheet2").Cells(rw,"B").Value = cell.value
rw = rw + 7
Next

--
Regards,
Tom Ogilvy


"SYBS" wrote:

Thanks for that, so I could use that number to represent different column
ranges, that will make a difference.

The 6/12th row explanation is. From my list of names on Sheet 1 I am
populating col B on 5 other sheets, 1 row to a name, but two other sheets use
6 rows per name, so the 1st name would be on the 13th line, 2nd name on 20th
line etc. At the moment I have simply coded the On activate for the list to
be copied and pasted to the other pages (where they take up one row). So I
am looking to put the names on the other sheets in Col B13/b20/b27 etc.

"Tom Ogilvy" wrote:

There are 33 columns in the range C:AI, so the 33 extends the range to
include these columns.

There is no reason to loop for the problem described unless you want the
code to run slower and be more inefficient.

What does every other 6th cell mean? Do you mean every 12th cell?

--
Regards,
Tom Ogilvy


"SYBS" wrote:

Hi Tom,

That works for me !!
I di try offset but thought it needed to be in a loop. Can you please
explain how the Resize(, 33).FillDown works and what is the significance
of the '33'.

For future reference, could I put the offset bit into a For each loop if
needed or is the offset more efficient.

Last question. If I want to put a name from a list into every other 6th cell
in a column, how do I phrase that ?

Thanks again, really appreciated

Sybs

"Tom Ogilvy" wrote:

Sub FillFormulas()
Dim rng As Range
Set rng = Range("B7", _
Cells(7, "B").End(xldown))
rng.Offset(0, 1).Resize(, 33).FillDown
End Sub

--
Regards,
Tom Ogilvy

"SYBS" wrote:

I am trying to copy one row with formula in it(C7:AI7), to the rows below it,
the number of rows to be copied down the sheet depends on the number of
filled cells in col B (max B7:B67). I.e. If there are four filled cells in
col B (7-11) then I want the range C7:AI7 to be copied down to match. Now
desperate. Anyone help please ?

Thanks in hope

Sybs

Thank you for that help. I am working on both areas and will let you know
how it goes.

Sybs


"Tom Ogilvy" wrote:

Dim list as Range, cell as range
Dim rw as long
rw = 13
set list = Worksheets("Sheet1").Range("B1:B20")
for each cell in list
worksheets("Sheet2").Cells(rw,"B").Value = cell.value
rw = rw + 7
Next

--
Regards,
Tom Ogilvy


"SYBS" wrote:

Thanks for that, so I could use that number to represent different column
ranges, that will make a difference.

The 6/12th row explanation is. From my list of names on Sheet 1 I am
populating col B on 5 other sheets, 1 row to a name, but two other sheets use
6 rows per name, so the 1st name would be on the 13th line, 2nd name on 20th
line etc. At the moment I have simply coded the On activate for the list to
be copied and pasted to the other pages (where they take up one row). So I
am looking to put the names on the other sheets in Col B13/b20/b27 etc.

"Tom Ogilvy" wrote:

There are 33 columns in the range C:AI, so the 33 extends the range to
include these columns.

There is no reason to loop for the problem described unless you want the
code to run slower and be more inefficient.

What does every other 6th cell mean? Do you mean every 12th cell?

--
Regards,
Tom Ogilvy


"SYBS" wrote:

Hi Tom,

That works for me !!
I di try offset but thought it needed to be in a loop. Can you please
explain how the Resize(, 33).FillDown works and what is the significance
of the '33'.

For future reference, could I put the offset bit into a For each loop if
needed or is the offset more efficient.

Last question. If I want to put a name from a list into every other 6th cell
in a column, how do I phrase that ?

Thanks again, really appreciated

Sybs

"Tom Ogilvy" wrote:

Sub FillFormulas()
Dim rng As Range
Set rng = Range("B7", _
Cells(7, "B").End(xldown))
rng.Offset(0, 1).Resize(, 33).FillDown
End Sub

--
Regards,
Tom Ogilvy

"SYBS" wrote:

I am trying to copy one row with formula in it(C7:AI7), to the rows below it,
the number of rows to be copied down the sheet depends on the number of
filled cells in col B (max B7:B67). I.e. If there are four filled cells in
col B (7-11) then I want the range C7:AI7 to be copied down to match. Now
desperate. Anyone help please ?

Thanks in hope

Sybs