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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
723
AlexanderBB
A
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
372
NdNoviceHlp
NdNoviceHlp
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
341
kvsrinivasamurthy
K
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
351
Fuji
F
H
  • Solved
Copy hidden row and paste unhidden after last row
Replies
6
Views
113
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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