Copying range of cells to another sheet
0
I want to copy a range of cells, say F10:F59, of the Form sheet, then transpose and paste them to another range on another sheet named Stock Manual Senin, say B11:BA25.
This is what I currently have:
Sub InputPAGS_Senin()
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim vntRange As Variant
Dim lastRow As Long
Set copySheet = Sheets("Form")
Set pasteSheet = Sheets("Stock Manual Senin")
' Calculate last row of data.
lastRow = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
' Copy 2 cells.
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 1) = copySheet.Range("N2").Value
' Paste column range into array.
vntRange = copySheet.Range("F10:F59").Value
' Paste transpose array into row range.
Sheets("Stock Manual Senin").Select
Range("B11:BA25").Select
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 3).Resize(, copySheet _
.Range("F10:F59").Rows.Count).Value = Application.Transpose(vntRange)
End Sub
the paste target should be in row 11, but it'd paste in row 285 cause target range is located between the others table's row.
Can anyone advise me on how I should continue please? Thank you.
excel vba
edited Jan 1 at 7:01
Cindy Meister
15.2k102235
asked Dec 31 '18 at 14:53
Luthfi TryWandoLuthfi TryWando
123
add a comment |
0
I want to copy a range of cells, say F10:F59, of the Form sheet, then transpose and paste them to another range on another sheet named Stock Manual Senin, say B11:BA25.
This is what I currently have:
Sub InputPAGS_Senin()
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim vntRange As Variant
Dim lastRow As Long
Set copySheet = Sheets("Form")
Set pasteSheet = Sheets("Stock Manual Senin")
' Calculate last row of data.
lastRow = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
' Copy 2 cells.
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 1) = copySheet.Range("N2").Value
' Paste column range into array.
vntRange = copySheet.Range("F10:F59").Value
' Paste transpose array into row range.
Sheets("Stock Manual Senin").Select
Range("B11:BA25").Select
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 3).Resize(, copySheet _
.Range("F10:F59").Rows.Count).Value = Application.Transpose(vntRange)
End Sub
the paste target should be in row 11, but it'd paste in row 285 cause target range is located between the others table's row.
Can anyone advise me on how I should continue please? Thank you.
excel vba
edited Jan 1 at 7:01
Cindy Meister
15.2k102235
asked Dec 31 '18 at 14:53
Luthfi TryWandoLuthfi TryWando
123
add a comment |
0
0
0
I want to copy a range of cells, say F10:F59, of the Form sheet, then transpose and paste them to another range on another sheet named Stock Manual Senin, say B11:BA25.
This is what I currently have:
Sub InputPAGS_Senin()
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim vntRange As Variant
Dim lastRow As Long
Set copySheet = Sheets("Form")
Set pasteSheet = Sheets("Stock Manual Senin")
' Calculate last row of data.
lastRow = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
' Copy 2 cells.
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 1) = copySheet.Range("N2").Value
' Paste column range into array.
vntRange = copySheet.Range("F10:F59").Value
' Paste transpose array into row range.
Sheets("Stock Manual Senin").Select
Range("B11:BA25").Select
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 3).Resize(, copySheet _
.Range("F10:F59").Rows.Count).Value = Application.Transpose(vntRange)
End Sub
the paste target should be in row 11, but it'd paste in row 285 cause target range is located between the others table's row.
Can anyone advise me on how I should continue please? Thank you.
excel vba
edited Jan 1 at 7:01
Cindy Meister
15.2k102235
asked Dec 31 '18 at 14:53
Luthfi TryWandoLuthfi TryWando
123
I want to copy a range of cells, say F10:F59, of the Form sheet, then transpose and paste them to another range on another sheet named Stock Manual Senin, say B11:BA25.
This is what I currently have:
Sub InputPAGS_Senin()
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim vntRange As Variant
Dim lastRow As Long
Set copySheet = Sheets("Form")
Set pasteSheet = Sheets("Stock Manual Senin")
' Calculate last row of data.
lastRow = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
' Copy 2 cells.
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 1) = copySheet.Range("N2").Value
' Paste column range into array.
vntRange = copySheet.Range("F10:F59").Value
' Paste transpose array into row range.
Sheets("Stock Manual Senin").Select
Range("B11:BA25").Select
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 3).Resize(, copySheet _
.Range("F10:F59").Rows.Count).Value = Application.Transpose(vntRange)
End Sub
the paste target should be in row 11, but it'd paste in row 285 cause target range is located between the others table's row.
Can anyone advise me on how I should continue please? Thank you.
Sub InputPAGS_Senin()
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim vntRange As Variant
Dim lastRow As Long
Set copySheet = Sheets("Form")
Set pasteSheet = Sheets("Stock Manual Senin")
' Calculate last row of data.
lastRow = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
' Copy 2 cells.
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 1) = copySheet.Range("N2").Value
' Paste column range into array.
vntRange = copySheet.Range("F10:F59").Value
' Paste transpose array into row range.
Sheets("Stock Manual Senin").Select
Range("B11:BA25").Select
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 3).Resize(, copySheet _
.Range("F10:F59").Rows.Count).Value = Application.Transpose(vntRange)
End SubSub InputPAGS_Senin()
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim vntRange As Variant
Dim lastRow As Long
Set copySheet = Sheets("Form")
Set pasteSheet = Sheets("Stock Manual Senin")
' Calculate last row of data.
lastRow = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
' Copy 2 cells.
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 1) = copySheet.Range("N2").Value
' Paste column range into array.
vntRange = copySheet.Range("F10:F59").Value
' Paste transpose array into row range.
Sheets("Stock Manual Senin").Select
Range("B11:BA25").Select
pasteSheet.Cells(lastRow + 1, 1).Offset(0, 3).Resize(, copySheet _
.Range("F10:F59").Rows.Count).Value = Application.Transpose(vntRange)
End Subexcel vba
excel vba
edited Jan 1 at 7:01
Cindy Meister
15.2k102235
asked Dec 31 '18 at 14:53
Luthfi TryWandoLuthfi TryWando
123
edited Jan 1 at 7:01
Cindy Meister
15.2k102235
asked Dec 31 '18 at 14:53
Luthfi TryWandoLuthfi TryWando
123
edited Jan 1 at 7:01
Cindy Meister
15.2k102235
edited Jan 1 at 7:01
Cindy Meister
15.2k102235
edited Jan 1 at 7:01
Cindy Meister
15.2k102235
15.2k102235
asked Dec 31 '18 at 14:53
Luthfi TryWandoLuthfi TryWando
123
asked Dec 31 '18 at 14:53
Luthfi TryWandoLuthfi TryWando
123
asked Dec 31 '18 at 14:53
Luthfi TryWandoLuthfi TryWando
123
123
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
xlUp Becomes xlDown
You have to calculate the last row from NAMA TOKO down (xlDown). Do not delete NAMA TOKO and PAGS / MIGO, then you can use the following
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Offset(1).Row
or even better
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Row + 1
answered Dec 31 '18 at 19:05
VBasic2008VBasic2008
2,7802415
Yess...ur Solution helping me Sir @VBasic2008...once again thank you very much.. :D
– Luthfi TryWando
Dec 31 '18 at 20:08
add a comment |
0
Does something like this work? I just did a manual pivot as I wrote out the values.
Sub SOTest()
Dim copySheet As Worksheet
Dim CopyRange As Range
Dim Cell As Range
Dim pasteSheet As Worksheet
Dim lastRow As Long
Dim ColIndex As Long
Set copySheet = ThisWorkbook.Worksheets("Form")
Set pasteSheet = ThisWorkbook.Worksheets("Stock Manual Senin")
Set CopyRange = copySheet.Range("F10:F59")
ColIndex = 2 'Column B
With pasteSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
End With
Application.ScreenUpdating = False
For Each Cell In CopyRange
pasteSheet.Cells(lastRow, ColIndex).Value = Cell.Value
ColIndex = ColIndex + 1
Next
Application.ScreenUpdating = True
End Sub
answered Dec 31 '18 at 15:53
Ryan WildryRyan Wildry
3,5121925
thank you for the coding Sir @Ryan Wildry..but the result not being in my expected row inrow 11. It's confusing because the target is located between the others table in same sheet..is there any advise ?
– Luthfi TryWando
Dec 31 '18 at 18:21
Then update lastrow to output on the desired row
– Ryan Wildry
Dec 31 '18 at 18:23
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
});
}
});
draft saved
draft discarded
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%2f53988744%2fcopying-range-of-cells-to-another-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
xlUp Becomes xlDown
You have to calculate the last row from NAMA TOKO down (xlDown). Do not delete NAMA TOKO and PAGS / MIGO, then you can use the following
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Offset(1).Row
or even better
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Row + 1
answered Dec 31 '18 at 19:05
VBasic2008VBasic2008
2,7802415
Yess...ur Solution helping me Sir @VBasic2008...once again thank you very much.. :D
– Luthfi TryWando
Dec 31 '18 at 20:08
add a comment |
0
xlUp Becomes xlDown
You have to calculate the last row from NAMA TOKO down (xlDown). Do not delete NAMA TOKO and PAGS / MIGO, then you can use the following
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Offset(1).Row
or even better
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Row + 1
answered Dec 31 '18 at 19:05
VBasic2008VBasic2008
2,7802415
Yess...ur Solution helping me Sir @VBasic2008...once again thank you very much.. :D
– Luthfi TryWando
Dec 31 '18 at 20:08
add a comment |
0
0
0
xlUp Becomes xlDown
You have to calculate the last row from NAMA TOKO down (xlDown). Do not delete NAMA TOKO and PAGS / MIGO, then you can use the following
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Offset(1).Row
or even better
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Row + 1
answered Dec 31 '18 at 19:05
VBasic2008VBasic2008
2,7802415
xlUp Becomes xlDown
You have to calculate the last row from NAMA TOKO down (xlDown). Do not delete NAMA TOKO and PAGS / MIGO, then you can use the following
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Offset(1).Row
or even better
lastRow = pasteSheet.Cells(9, 2).End(xlDown).Row + 1
answered Dec 31 '18 at 19:05
VBasic2008VBasic2008
2,7802415
answered Dec 31 '18 at 19:05
VBasic2008VBasic2008
2,7802415
answered Dec 31 '18 at 19:05
VBasic2008VBasic2008
2,7802415
answered Dec 31 '18 at 19:05
VBasic2008VBasic2008
2,7802415
2,7802415
Yess...ur Solution helping me Sir @VBasic2008...once again thank you very much.. :D
– Luthfi TryWando
Dec 31 '18 at 20:08
add a comment |
Yess...ur Solution helping me Sir @VBasic2008...once again thank you very much.. :D
– Luthfi TryWando
Dec 31 '18 at 20:08
Yess...ur Solution helping me Sir @VBasic2008...once again thank you very much.. :D
– Luthfi TryWando
Dec 31 '18 at 20:08
Yess...ur Solution helping me Sir @VBasic2008...once again thank you very much.. :D
– Luthfi TryWando
Dec 31 '18 at 20:08
add a comment |
0
Does something like this work? I just did a manual pivot as I wrote out the values.
Sub SOTest()
Dim copySheet As Worksheet
Dim CopyRange As Range
Dim Cell As Range
Dim pasteSheet As Worksheet
Dim lastRow As Long
Dim ColIndex As Long
Set copySheet = ThisWorkbook.Worksheets("Form")
Set pasteSheet = ThisWorkbook.Worksheets("Stock Manual Senin")
Set CopyRange = copySheet.Range("F10:F59")
ColIndex = 2 'Column B
With pasteSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
End With
Application.ScreenUpdating = False
For Each Cell In CopyRange
pasteSheet.Cells(lastRow, ColIndex).Value = Cell.Value
ColIndex = ColIndex + 1
Next
Application.ScreenUpdating = True
End Sub
answered Dec 31 '18 at 15:53
Ryan WildryRyan Wildry
3,5121925
thank you for the coding Sir @Ryan Wildry..but the result not being in my expected row inrow 11. It's confusing because the target is located between the others table in same sheet..is there any advise ?
– Luthfi TryWando
Dec 31 '18 at 18:21
Then update lastrow to output on the desired row
– Ryan Wildry
Dec 31 '18 at 18:23
add a comment |
0
Does something like this work? I just did a manual pivot as I wrote out the values.
Sub SOTest()
Dim copySheet As Worksheet
Dim CopyRange As Range
Dim Cell As Range
Dim pasteSheet As Worksheet
Dim lastRow As Long
Dim ColIndex As Long
Set copySheet = ThisWorkbook.Worksheets("Form")
Set pasteSheet = ThisWorkbook.Worksheets("Stock Manual Senin")
Set CopyRange = copySheet.Range("F10:F59")
ColIndex = 2 'Column B
With pasteSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
End With
Application.ScreenUpdating = False
For Each Cell In CopyRange
pasteSheet.Cells(lastRow, ColIndex).Value = Cell.Value
ColIndex = ColIndex + 1
Next
Application.ScreenUpdating = True
End Sub
answered Dec 31 '18 at 15:53
Ryan WildryRyan Wildry
3,5121925
thank you for the coding Sir @Ryan Wildry..but the result not being in my expected row inrow 11. It's confusing because the target is located between the others table in same sheet..is there any advise ?
– Luthfi TryWando
Dec 31 '18 at 18:21
Then update lastrow to output on the desired row
– Ryan Wildry
Dec 31 '18 at 18:23
add a comment |
0
0
0
Does something like this work? I just did a manual pivot as I wrote out the values.
Sub SOTest()
Dim copySheet As Worksheet
Dim CopyRange As Range
Dim Cell As Range
Dim pasteSheet As Worksheet
Dim lastRow As Long
Dim ColIndex As Long
Set copySheet = ThisWorkbook.Worksheets("Form")
Set pasteSheet = ThisWorkbook.Worksheets("Stock Manual Senin")
Set CopyRange = copySheet.Range("F10:F59")
ColIndex = 2 'Column B
With pasteSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
End With
Application.ScreenUpdating = False
For Each Cell In CopyRange
pasteSheet.Cells(lastRow, ColIndex).Value = Cell.Value
ColIndex = ColIndex + 1
Next
Application.ScreenUpdating = True
End Sub
answered Dec 31 '18 at 15:53
Ryan WildryRyan Wildry
3,5121925
Does something like this work? I just did a manual pivot as I wrote out the values.
Sub SOTest()
Dim copySheet As Worksheet
Dim CopyRange As Range
Dim Cell As Range
Dim pasteSheet As Worksheet
Dim lastRow As Long
Dim ColIndex As Long
Set copySheet = ThisWorkbook.Worksheets("Form")
Set pasteSheet = ThisWorkbook.Worksheets("Stock Manual Senin")
Set CopyRange = copySheet.Range("F10:F59")
ColIndex = 2 'Column B
With pasteSheet
lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
End With
Application.ScreenUpdating = False
For Each Cell In CopyRange
pasteSheet.Cells(lastRow, ColIndex).Value = Cell.Value
ColIndex = ColIndex + 1
Next
Application.ScreenUpdating = True
End Sub
answered Dec 31 '18 at 15:53
Ryan WildryRyan Wildry
3,5121925
answered Dec 31 '18 at 15:53
Ryan WildryRyan Wildry
3,5121925
answered Dec 31 '18 at 15:53
Ryan WildryRyan Wildry
3,5121925
answered Dec 31 '18 at 15:53
Ryan WildryRyan Wildry
3,5121925
3,5121925
thank you for the coding Sir @Ryan Wildry..but the result not being in my expected row inrow 11. It's confusing because the target is located between the others table in same sheet..is there any advise ?
– Luthfi TryWando
Dec 31 '18 at 18:21
Then update lastrow to output on the desired row
– Ryan Wildry
Dec 31 '18 at 18:23
add a comment |
thank you for the coding Sir @Ryan Wildry..but the result not being in my expected row inrow 11. It's confusing because the target is located between the others table in same sheet..is there any advise ?
– Luthfi TryWando
Dec 31 '18 at 18:21
Then update lastrow to output on the desired row
– Ryan Wildry
Dec 31 '18 at 18:23
thank you for the coding Sir @Ryan Wildry..but the result not being in my expected row in
row 11. It's confusing because the target is located between the others table in same sheet..is there any advise ?– Luthfi TryWando
Dec 31 '18 at 18:21
thank you for the coding Sir @Ryan Wildry..but the result not being in my expected row in
row 11. It's confusing because the target is located between the others table in same sheet..is there any advise ?– Luthfi TryWando
Dec 31 '18 at 18:21
Then update lastrow to output on the desired row
– Ryan Wildry
Dec 31 '18 at 18:23
Then update lastrow to output on the desired row
– Ryan Wildry
Dec 31 '18 at 18:23
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53988744%2fcopying-range-of-cells-to-another-sheet%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
