Change this line:

..Range("C1", .Cells(1, .Columns.Count)).Clear

to

..Range("C1", .Cells(1, .Columns.Count)).Entirecolumn.Clear



Dave Peterson wrote:

You could use a worksheet event. And since the values are updated via formulas,
you can use the _calculate event.

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this in:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
Next iRow
End With
End Sub

The code will have to go behind each worksheet that needs it.

Myriam wrote:

Thanks for the reply.
No, the Sheet will not be active, it is a background calculations sheet.
Column A and B will be linked to cells in other worksheets.
When cells in A and B change, I need to populate the necessary columns
utilizing code The results are used for additional calculation within that
Sheet.
Can this be done?
Regards,

"Dave Peterson" wrote:

If you want to run the code against the sheet that's active, you could change
this line:

With Worksheets("sheet1")
to
With Activesheet

(in either suggested code)

Myriam wrote:

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson