The probelm is the way you are addressing the name which originally you said were in the range D3 to D8, in the code I wrote I used the
Range(cells(row, col), cells(row,col)) addressing mode, this is to make it easy to use an index
newname = Worksheets("sheet1").Range(Cells(i, 4), Cells(i, 4))
the index loop goes from 3 to 8 and the column is col 4, ( A,B,C,D that makes D column 4)
so my code picks up D3 then D4 then D5, etc.
Your code is not picking up anything like what you want
newname = Worksheets("sheet1").Range(Cells(1, i), Cells(1, i))
this will pick in sequence Row 1, column 3 (C1) , thne Row 1 column 4 ( D1), then (E1)
I think what you want to do do now is very simple , you need to concatenate C3 and D3 , thenn C4 and D4
so change the code to :
newname = Worksheets("sheet1").Range(Cells(i, 3), Cells(i, 3)) & Worksheets("sheet1").Range(Cells(i, 4), Cells(i, 4))