Repeating my code until the last row of data

[KNKN9]

Hi ,

I have the data set

	COL A	COL B	COL C	COL D	COL E
ROW1	DATA		DATA		DATA
ROW2	DATA		DATA		DATA
ROW3	DATA		DATA		DATA
ROW4	DATA		DATA		DATA
ROW5	DATA		DATA		DATA
ROW6	DATA		DATA		DATA
ROW7	DATA		DATA		DATA

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

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.

<colgroup><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody></tbody>

 

[Steve_]

Hi ,


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 ?

Are you asking to have this code run against the last row of your sheet or are you asking for this code to run on every line of your sheet, all the way down to the last row?

 

[KNKN9]

Thank you for your response and i want the code to run on every line of my sheet, all the way down to the last row

Are you asking to have this code run against the last row of your sheet or are you asking for this code to run on every line of your sheet, all the way down to the last row?

 

[KNKN9]

If someone can help with this. That'll be much appreciated.

 

[Joe4]

Which column can we look at that will always have data so that we can use that column to determine where the last row of data is?
Will column A always be populated, or will it be some other column?

 

[KNKN9]

column A

 

[Joe4]

So then you can find the last row on a sheet like this:

Dim lr as Long
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

So then if you want to copy columns A:L down to last row, you could make the following change to your code:

ActiveSheet.Range("A3:L" & lr).Copy

 

[KNKN9]

Thanks...

However, I need A3 to change also ( so drop down each row).. What I am trying to do is change my table above from

	COL A	COL B	COLC	COL D	COL E
ROW 1		Role 1	Role 1	Role 1	Role 1	Role 1
ROW 2		String 1	String 2	String 3	String 4	String 5
ROW 3	project 1	values	values	values	values	values
ROW 4	project 2	values	values	values	values	values
ROW 5	project 3	values	values	values	values	values
ROW 6	project 4	values	values	values	values	values

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

to

	COL A	COL B	COLC	COL D	COL E
ROW 1	project 1	Role 1	String 1	values
ROW 2		Role 1	String 2	values
ROW 3		Role 1	String 3	values
ROW 4		Role 1	String 4	values
ROW 5		Role 1	String 5	values
ROW 6	project 2	Role 1	String 1	values
		Role 1	String 2	values
		Role 1	String 3	values
		Role 1	String 4	values
		Role 1	String 5	values

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

So then you can find the last row on a sheet like this:

Dim lr as Long
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

So then if you want to copy columns A:L down to last row, you could make the following change to your code:

ActiveSheet.Range("A3:L" & lr).Copy

 

[Joe4]

Your code would cause errors, even on the first record, unless there is already something on Sheet4 to start. Otherwise, this row would bomb out, as it goes to the bottom of the page, and then tries to move one row past that:

Sheets("Sheet4").Range("N1").End(xlDown).Offset(1, -13).PasteSpecial Paste:=xlPasteValues

So, what does Sheet4 look like to start, before this macro runs?

 

[KNKN9]

Yes therebis data is sheet4
So
COL COL M COL N
Header 1 header 2 header 3 .....
Value value value
Blank blank value