Repeating my code until the last row of data

K

KNKN9

Board Regular
Joined
Mar 27, 2017
Messages
92
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 ?



Code: 
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]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
KNKN9 said:
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 ?
Click to expand...


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?
 
Last edited:
Upvote 0
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
Steve_ said:
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?
Click to expand...
 
Upvote 0
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?
 
Upvote 0
So then you can find the last row on a sheet like this:
Code: 
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:
Code: 
ActiveSheet.Range("A3:L" & lr).Copy
 
Upvote 0
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

[TABLE="width: 448"]
<colgroup><col width="64" span="7" 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"]COLC[/TD]
[TD="width: 64"]COL D[/TD]
[TD="width: 64"]COL E[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]ROW 1 [/TD]
[TD][/TD]
[TD]Role 1[/TD]
[TD]Role 1[/TD]
[TD]Role 1[/TD]
[TD]Role 1[/TD]
[TD]Role 1[/TD]
[/TR]
[TR]
[TD]ROW 2[/TD]
[TD][/TD]
[TD]String 1[/TD]
[TD]String 2[/TD]
[TD]String 3[/TD]
[TD]String 4[/TD]
[TD]String 5[/TD]
[/TR]
[TR]
[TD]ROW 3[/TD]
[TD]project 1[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[/TR]
[TR]
[TD]ROW 4[/TD]
[TD]project 2[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[/TR]
[TR]
[TD]ROW 5[/TD]
[TD]project 3[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[/TR]
[TR]
[TD]ROW 6[/TD]
[TD]project 4[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[TD]values[/TD]
[/TR]
</tbody>[/TABLE]


to

[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"]COLC[/TD]
[TD="width: 64"]COL D[/TD]
[TD="width: 64"]COL E[/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]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Joe4 said:
So then you can find the last row on a sheet like this:
Code: 
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:
Code: 
ActiveSheet.Range("A3:L" & lr).Copy
Click to expand...
 
Upvote 0
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:
Code: 
Sheets("Sheet4").Range("N1").End(xlDown).Offset(1, -13).PasteSpecial Paste:=xlPasteValues
So, what does Sheet4 look like to start, before this macro runs?
 
Last edited:
Upvote 0
Yes therebis data is sheet4
So
COL COL M COL N
Header 1 header 2 header 3 .....
Value value value
Blank blank value
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Last Col in Range
4 5 6
Replies
52
Views
660
AlexanderBB
A
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
298
kvsrinivasamurthy
K
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
250
mumps
mumps
L
  • Question Question
Sending Email with Table in Body
Replies
3
Views
519
Domenic
D
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
648
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top