Copy cell value across number of columns based upon cell value

[chally3]

Good morning all,

I'm trying to develop an inventory reduction forecast spreadsheet in a pivot table format for ease of use - but am really stuck with 1 part to add in the data.

What I want to do is copy a cell value that is calculated (weekly forecast inventory reduction),across a number of columns based upon another cell vale (number of weeks stock left). IE For instance cell AE2 has a weekly inventory burn off rate of $100 & cell AG2 is a calculation that says we have for instance "3" weeks of stock.

What I could like to do is copy the value on AE2 (same for every row) across the number of columns shown in AG2 (starting from Column AH onward).

Hopefully this will explain it AE2 is $100 I(weekly inventory decrease) AG2 is 3 (3 weeks stock) , so in AH2,AI2,AJ2 copy the $100 in those cells (row 1 will be week numbers AH1 onwards) so on through the spreadsheet for each row with data in. If AG2 was 5, copy $100 in AH2,AI2,AJ2,AK2,AL2 etc

Any help much appreciated, I've tried to use the current data in a pivot table - but can't work out how to plot the value each week.

Thank you and good luck

 

[Ffidrac]

my first thought is an IF statement using a column headers with the week number, 1,2,3 etc so something like:
=IF($AEG$2>=AH2,$AE$2,"")
Then copy formula across cols

 

[chally3]

Hi,I'm afraid that did not work

 

[mumps]

Try this macro:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("AE2:AE" & LastRow)
        Range("AH" & rng.Row).Resize(, Range("AG" & rng.Row).Value) = rng
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[chally3]

Mumps, that is perfection!! Thank you very very much - this solves a massive headache for me!

 

[mumps]

My pleasure.