Repeating my code until the last row of data

[KNKN9]

Hi ,

I have the data set

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]COL A[/TD]
[TD="width: 64"]COL B[/TD]
[TD="width: 64"]COL C[/TD]
[TD="width: 64"]COL D[/TD]
[TD="width: 64"]COL E[/TD]
[/TR]
[TR]
[TD]ROW1[/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[/TR]
[TR]
[TD]ROW2[/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[/TR]
[TR]
[TD]ROW3[/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[/TR]
[TR]
[TD]ROW4[/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[/TR]
[TR]
[TD]ROW5[/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[/TR]
[TR]
[TD]ROW6[/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[/TR]
[TR]
[TD]ROW7[/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD]DATA[/TD]
[/TR]
</tbody>[/TABLE]


At the moment my code copies the information from row 1. However, I need this to happen for the last row of a spreadsheet. As the last row differs from each spreadsheet.

Can someone help amend my code below so it is able to function for data until the last row ?

Sub data()


'copying projects
ActiveSheet.Range("A3:L3").Copy
Sheets("Sheet4").Range("N1").End(xlDown).Offset(1, -13).PasteSpecial Paste:=xlPasteValues


With Sheets("sheet1")
    Set rng = .Range(.Cells(1, 15), .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
    Set rng1 = .Range(.Cells(2, 15), .Cells(2, .Cells(2, .Columns.Count).End(xlToLeft).Column))
    Set rng_3 = .Range(.Cells(3, 13), .Cells(3, .Cells(3, .Columns.Count).End(xlToLeft).Column))
End With


'copying roles and planned hours


rng.Copy
Sheets("Sheet4").Range("N1").End(xlDown).Offset(0, -1).PasteSpecial xlValues, Transpose:=True
rng1.Copy
Sheets("Sheet4").Range("N1").End(xlDown).Offset(1, 0).PasteSpecial xlValues, Transpose:=True


'copy_actual hours
rng_3.Copy
Sheets("Sheet4").Range("N1").End(xlDown).Offset(-51, 1).PasteSpecial xlValues, Transpose:=True




End Sub

Thanks in advanced.



[TABLE="width: 1663"]
<colgroup><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]

 

[Joe4]

I am trying to set-up your exact scenario so I can test it, but it is not working, as you have other lines of code that blows things up for me, like this one:

Sheets("Sheet4").Range("N1").End(xlDown).Offset(-51, 1).PasteSpecial xlValues, Transpose:=True

Because of that Offset command, unless there is data in column N down to at least row 52 at the very start, this line of code will cause errors.

 

[KNKN9]

[TABLE="class: cms_table, width: 384"]
<tbody>[TR]
[TD="width: 64"]COL L[/TD]
[TD="width: 64"]COL M[/TD]
[TD="width: 64"]COLN[/TD]
[TD="width: 64"]COL O[/TD]
[TD="width: 64"]COL P[/TD]
[/TR]
[TR]
[TD]ROW 1[/TD]
[TD]project 1[/TD]
[TD]Role 1[/TD]
[TD]String 1[/TD]
[TD]values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW 2[/TD]
[TD][/TD]
[TD]Role 1[/TD]
[TD]String 2[/TD]
[TD]values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW 3[/TD]
[TD][/TD]
[TD]Role 1[/TD]
[TD]String 3[/TD]
[TD]values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW 4[/TD]
[TD][/TD]
[TD]Role 1[/TD]
[TD]String 4[/TD]
[TD]values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW 5[/TD]
[TD][/TD]
[TD]Role 1[/TD]
[TD]String 5[/TD]
[TD]values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW 6[/TD]
[TD]project 2[/TD]
[TD]Role 1[/TD]
[TD]String 1[/TD]
[TD]values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Role 1[/TD]
[TD]String 2[/TD]
[TD]values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Role 1[/TD]
[TD]String 3[/TD]
[TD]values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Role 1[/TD]
[TD]String 4[/TD]
[TD]values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Role 1[/TD]
[TD]String 5[/TD]
[TD]values[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table, width: 384"]
<tbody>[TR]
[TD][/TD]
[TD]
Col N gets pasted first then i need col M to start where col N starts hence the offset 51[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

I am sorry, I don't think I can help much further. I think that without having a copy of your data to see what you are working with, and without a clear explanation of how you want all this to work, it is extremely difficult to help you.

My intention was to answer your question about how to find the last row of data (which I did). I assumed that the rest of the code was already working properly, and it was just that part you needed help with. But it appears that may not be the case (or at least not with how I am trying to set up the data baased on your replies).

If you do actually have it working the way you want for a set number of rows, then upload a copy of your workbook and provide a link to it, and I will try to download it later and see if I can make your code dynamic.