Copy data until last row in a new sheet and always exclude the last 4 rows

Multi tool use
I'm having a hard time trying to create a macro that copy the table from sheet 1 witch is defined by the area D21:O21 until the end – (minus) the last 4 lines.
I'm also having problems to paste results because in sheet 2 I have a table that feeds pivot charts. After I delete previous filled rows (with values) and paste new ones (new values) the table format seems to extend much further than it is supposed to do (meaning it adds blank cells downstream as if the copyed sheet 1 had a lot more rows with values on it).
For example: Imagine that my table (in sheet 1) happens to have 600 rows with values. If I copy to the table (table format in sheet 2) I end up by extend the table in sheet 2 much further than 600 rows (aprox 10000). And this is not what i want... Instead of adding multiple empty lines i want the opposite: from those 600 i want to copy all except last 4 lines. = from top to 596 rows.
I have this code. It's pretty effective apart from those two caveats - not beeing able to disregard last 4 lines and extend more than it should when copied:
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O21" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
Can you help me please?
excel vba excel-vba
add a comment |
I'm having a hard time trying to create a macro that copy the table from sheet 1 witch is defined by the area D21:O21 until the end – (minus) the last 4 lines.
I'm also having problems to paste results because in sheet 2 I have a table that feeds pivot charts. After I delete previous filled rows (with values) and paste new ones (new values) the table format seems to extend much further than it is supposed to do (meaning it adds blank cells downstream as if the copyed sheet 1 had a lot more rows with values on it).
For example: Imagine that my table (in sheet 1) happens to have 600 rows with values. If I copy to the table (table format in sheet 2) I end up by extend the table in sheet 2 much further than 600 rows (aprox 10000). And this is not what i want... Instead of adding multiple empty lines i want the opposite: from those 600 i want to copy all except last 4 lines. = from top to 596 rows.
I have this code. It's pretty effective apart from those two caveats - not beeing able to disregard last 4 lines and extend more than it should when copied:
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O21" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
Can you help me please?
excel vba excel-vba
1
Have you looked atResize
method on theRange
object? AlsoD21:O21
is a single row of data so not sure what you mean by discarding the last four rows?
– Alex P
Jan 2 at 15:50
ws1.Range("D21:O21" & Last_Row1)
is probably wrong, did you meanws1.Range("D21:O" & Last_Row1)
? You can just subtract 4 from Last_Row1 then.
– Vincent G
Jan 2 at 15:57
Vincent G Thaks It worked.
– jps17183
Jan 2 at 16:14
add a comment |
I'm having a hard time trying to create a macro that copy the table from sheet 1 witch is defined by the area D21:O21 until the end – (minus) the last 4 lines.
I'm also having problems to paste results because in sheet 2 I have a table that feeds pivot charts. After I delete previous filled rows (with values) and paste new ones (new values) the table format seems to extend much further than it is supposed to do (meaning it adds blank cells downstream as if the copyed sheet 1 had a lot more rows with values on it).
For example: Imagine that my table (in sheet 1) happens to have 600 rows with values. If I copy to the table (table format in sheet 2) I end up by extend the table in sheet 2 much further than 600 rows (aprox 10000). And this is not what i want... Instead of adding multiple empty lines i want the opposite: from those 600 i want to copy all except last 4 lines. = from top to 596 rows.
I have this code. It's pretty effective apart from those two caveats - not beeing able to disregard last 4 lines and extend more than it should when copied:
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O21" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
Can you help me please?
excel vba excel-vba
I'm having a hard time trying to create a macro that copy the table from sheet 1 witch is defined by the area D21:O21 until the end – (minus) the last 4 lines.
I'm also having problems to paste results because in sheet 2 I have a table that feeds pivot charts. After I delete previous filled rows (with values) and paste new ones (new values) the table format seems to extend much further than it is supposed to do (meaning it adds blank cells downstream as if the copyed sheet 1 had a lot more rows with values on it).
For example: Imagine that my table (in sheet 1) happens to have 600 rows with values. If I copy to the table (table format in sheet 2) I end up by extend the table in sheet 2 much further than 600 rows (aprox 10000). And this is not what i want... Instead of adding multiple empty lines i want the opposite: from those 600 i want to copy all except last 4 lines. = from top to 596 rows.
I have this code. It's pretty effective apart from those two caveats - not beeing able to disregard last 4 lines and extend more than it should when copied:
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O21" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
Can you help me please?
excel vba excel-vba
excel vba excel-vba
asked Jan 2 at 15:48
jps17183jps17183
155
155
1
Have you looked atResize
method on theRange
object? AlsoD21:O21
is a single row of data so not sure what you mean by discarding the last four rows?
– Alex P
Jan 2 at 15:50
ws1.Range("D21:O21" & Last_Row1)
is probably wrong, did you meanws1.Range("D21:O" & Last_Row1)
? You can just subtract 4 from Last_Row1 then.
– Vincent G
Jan 2 at 15:57
Vincent G Thaks It worked.
– jps17183
Jan 2 at 16:14
add a comment |
1
Have you looked atResize
method on theRange
object? AlsoD21:O21
is a single row of data so not sure what you mean by discarding the last four rows?
– Alex P
Jan 2 at 15:50
ws1.Range("D21:O21" & Last_Row1)
is probably wrong, did you meanws1.Range("D21:O" & Last_Row1)
? You can just subtract 4 from Last_Row1 then.
– Vincent G
Jan 2 at 15:57
Vincent G Thaks It worked.
– jps17183
Jan 2 at 16:14
1
1
Have you looked at
Resize
method on the Range
object? Also D21:O21
is a single row of data so not sure what you mean by discarding the last four rows?– Alex P
Jan 2 at 15:50
Have you looked at
Resize
method on the Range
object? Also D21:O21
is a single row of data so not sure what you mean by discarding the last four rows?– Alex P
Jan 2 at 15:50
ws1.Range("D21:O21" & Last_Row1)
is probably wrong, did you mean ws1.Range("D21:O" & Last_Row1)
? You can just subtract 4 from Last_Row1 then.– Vincent G
Jan 2 at 15:57
ws1.Range("D21:O21" & Last_Row1)
is probably wrong, did you mean ws1.Range("D21:O" & Last_Row1)
? You can just subtract 4 from Last_Row1 then.– Vincent G
Jan 2 at 15:57
Vincent G Thaks It worked.
– jps17183
Jan 2 at 16:14
Vincent G Thaks It worked.
– jps17183
Jan 2 at 16:14
add a comment |
3 Answers
3
active
oldest
votes
Try this:
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
add a comment |
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
add a comment |
You can use Offset function to exclude the last 4 rows.
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row
When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:
ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009244%2fcopy-data-until-last-row-in-a-new-sheet-and-always-exclude-the-last-4-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try this:
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
add a comment |
Try this:
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
add a comment |
Try this:
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
Try this:
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long, table As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Set table = ws1.Range("D21:O28") // I arbitrarily set this to 8 rows
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
table.Resize(table.Rows.Count - 4, table.Columns.Count).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
answered Jan 2 at 15:58
Alex PAlex P
10k44562
10k44562
add a comment |
add a comment |
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
add a comment |
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
add a comment |
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
Sub Prime()
Dim Last_Row1 As Long, Last_Row2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Enter DATA here")
Set ws2 = Sheets("DATA")
Application.ScreenUpdating = False
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
Last_Row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row ' Determine the next empty row in order to paste the data
ws1.Range("D21:O" & Last_Row1-4).Copy ws2.Range("A" & Last_Row2)
Application.ScreenUpdating = True
End Sub
answered Jan 2 at 16:15
jps17183jps17183
155
155
add a comment |
add a comment |
You can use Offset function to exclude the last 4 rows.
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row
When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:
ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
add a comment |
You can use Offset function to exclude the last 4 rows.
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row
When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:
ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
add a comment |
You can use Offset function to exclude the last 4 rows.
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row
When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:
ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
You can use Offset function to exclude the last 4 rows.
Last_Row1 = ws1.Range("C" & Rows.Count).End(xlUp).offset(-4,0).Row
When you copy the range, you should delete row indicator "21" behind column "O", otherwise, the code would not select till the last row. Like following:
ws1.Range("D21:O" & Last_Row1).Copy ws2.Range("A" & Last_Row2)
answered Jan 2 at 16:23
G.CG.C
32
32
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009244%2fcopy-data-until-last-row-in-a-new-sheet-and-always-exclude-the-last-4-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
PV5DrLZMG4AJfwH8RVOBLGA6T47kyMMnrmf,IyaA5LCM8U cyc8uZoftYCUut8LsW 8
1
Have you looked at
Resize
method on theRange
object? AlsoD21:O21
is a single row of data so not sure what you mean by discarding the last four rows?– Alex P
Jan 2 at 15:50
ws1.Range("D21:O21" & Last_Row1)
is probably wrong, did you meanws1.Range("D21:O" & Last_Row1)
? You can just subtract 4 from Last_Row1 then.– Vincent G
Jan 2 at 15:57
Vincent G Thaks It worked.
– jps17183
Jan 2 at 16:14