Looping through a set Spefiic set Range and Pasting in next empty cell in another column

[KNKN9]

Hi,

I have this code below, which selects a range in each line and paste the data in another sheet in column E in the next empty cell.

I do this for an offset up to 116 and this may increase. I am struggling to find a loop code that will loop through a set range and paste the data in Column E in the next empty cell.

Sheets("Brands_2018 FC").Range("W10:AC10").Copy
Sheets("Sheet9").Range("E2").PasteSpecial Transpose:=True
Sheets("Brands_2018 FC").Range("W10:AC10").Offset(1, 0).Copy
Sheets("Sheet9").Range("E2").End(xlDown).Offset(1, 0).PasteSpecial Transpose:=True
Sheets("Brands_2018 FC").Range("W10:AC10").Offset(2, 0).Copy
Sheets("Sheet9").Range("E2").End(xlDown).Offset(1, 0).PasteSpecial Transpose:=True

Thank you very much for your help in advanced !

 

[Joe4]

How do you determine the number of rows to transpose?
Let's say we can use column W to find the last row with data, like this:

Dim rw as Long
rw = [COLOR=#333333]Sheets("Brands_2018 FC").[/COLOR]Cells(Rows.Count,"W").End(xlUp).Row

Then you could use a loop something like:

For i = 10 to rw
    [COLOR=#333333]Sheets("Brands_2018 FC").Range(Cells(i, "W"), Cells(i,"AC")).Copy
    [/COLOR][COLOR=#333333]Sheets("Sheet9").Range("E2").End(xlDown).Offset(1, 0).PasteSpecial Transpose:=True[/COLOR][COLOR=#333333]
[/COLOR][COLOR=#333333]Next i[/COLOR]

 

[KNKN9]

Thank you for this. I have only just not been able to test this out and it works perfectly as needed.

Much Appreciated!

How do you determine the number of rows to transpose?
Let's say we can use column W to find the last row with data, like this:

Dim rw as Long
rw = [COLOR=#333333]Sheets("Brands_2018 FC").[/COLOR]Cells(Rows.Count,"W").End(xlUp).Row

Then you could use a loop something like:

For i = 10 to rw
    [COLOR=#333333]Sheets("Brands_2018 FC").Range(Cells(i, "W"), Cells(i,"AC")).Copy
    [/COLOR][COLOR=#333333]Sheets("Sheet9").Range("E2").End(xlDown).Offset(1, 0).PasteSpecial Transpose:=True[/COLOR][COLOR=#333333]
[/COLOR][COLOR=#333333]Next i[/COLOR]

 

[Joe4]

You are welcome.