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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
676
AlexanderBB
A
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
183
NdNoviceHlp
NdNoviceHlp
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
313
kvsrinivasamurthy
K
W
  • Solved
Looking For Tips To Improve My VBA
Replies
8
Views
198
who am i
W
H
  • Solved
Macro to speedy up process when copying Data
Replies
3
Views
75
Logit
Logit

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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