Using VBA to paste data to a destination cell which is defined by a different cell value

[BartWV]

Hi all,
My first question here. Have searched all over the internet for an answer, but have not been able to find one.
I am creating a file similar to a gantt chart to show the duration of multiple projects.

Project 1 starts on week1. Each subsequent project starts xx number of weeks later.
I have a range (ProjectHours) of 10 cells to be copied into each column of the corresponding project, at the corresponding week based on the defined frequency.

Ideally the chart is updated whenever the 'Number of projects' or 'Frequency' or updated.

I have used the Range_Offset function, but this requires the offset to be predefined.
I am looking for a way to incorporate Indirect to define the number of rows to offset.

Range("ProjectHours").Select
Selection.Copy
Range("B2").Offset(5, 1).Select
ActiveSheet.Paste

Many thanks for your help!

 

[mohadin]

Hi
Welcome the the forum
What about

Sub test()
    Dim i As Long
    Dim x As Long
    Fr = Range("I2").Value
    Range("b2:b11").Copy
    For i = 1 To Range("I1") - 1
        Range("b2").Offset(x + Fr, i).PasteSpecial
        x = Fr + x
    Next
End Sub

 

[BartWV]

Hi Mohadin,
Thanks so much for your reply.
I have tried it out and it looks to do exactly what I need.
Thanks!

 

[mohadin]

You are welcome
And thankyou for the feedback
Be happy and safe

 

[BartWV]

Hi Mohadin,

I have a follow up question.
Is there a way to loop the macro?
Instead of listing the 5 projects in B1:F1, I have changed it to project managers in B1:D1.
In this example there are now 6 projects, but only 3 project managers.
I would like the project hours for the 4th project to be allocated to project manager 1, the 5th project to project manager 2, and so on, still following the 5 week interval.

I have tried it by creating a second macro, and instead of using "I2" as the range I used "L3" (=I2*I3).
Fr = Range("L3").Value

Sub Macro1()
    Dim i As Long
    Dim x As Long
    Fr = Range("I2").Value
    Range("ProjectHours").Copy
    For i = 1 To Range("L2") - 1
        Range("b2").Offset(x + Fr, i).PasteSpecial
        x = Fr + x
    Next
End Sub
Sub Macro2()
    Dim i As Long
    Dim x As Long
    Fr = Range("L3").Value
    Range("ProjectHours").Copy
    For i = 1 To Range("L2") - 1
        Range("b2").Offset(x + Fr, i).PasteSpecial
        x = Fr + x
    Next
End Sub

This does not seem to work the way I want it to and instead copies project 4 to project manager 2 with a 15 week interval.
I feel like I am close, but missing a key step.

Thanks so much for your help!