Set column range based on cell value

[AdamCons]

Hi All

I've searched all day, but I cannot find answer (or I'm too lame to understand). I want to copy values from column from one sheet ('New week'!B) to column 'Week'!XX. Range 'Week'!XX has to be dynamic and should read column name from cell 'New week"!N18 (values in cell DI, AZ - outcome 'Week'!DI, 'Week'!AZ etc.).

I'll appreciate yours help.

Non dynamic code

Sub CopyWeek()

Dim j
j = 5

Do Until Range("'New week'!A" & j) = ""

Range("'Week'!DI" & j) = Range("'New week'!B" & j)

j = j + 1
Loop

End Sub

 

[AdamCons]

I made solution code for 3 columns. Can anyone check if it can be write in better way? I'm just doing first steps.

Sub CopyWeek()

Dim j
j = 5

Dim qty As Variant
qty = Range("'New week'!O13")

Dim qtybxs As Variant
qtybxs = Range("'New week'!P13")

Dim qtyplts As Variant
qtyplts = Range("'New week'!Q13")


Do Until Range("'New week'!A" & j) = ""

Worksheets("Week").Cells(j, qty).Value = Range("'New week'!B" & j)

Worksheets("Week").Cells(j, qtybxs).Value = Range("'New week'!C" & j)

Worksheets("Week").Cells(j, qtyplts).Value = Range("'New week'!D" & j)

j = j + 1
Loop

End Sub