VBA Transposing Loop Source Issue [duplicate]
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
This question is an exact duplicate of:
Transpose static range in a loop
1 answer
Thanks for taking the time to read my post. I am attempting to write my first VBA macro and am getting myself confused about what to do next. I found a few pieces of code online that I cobbled together, which work perfectly on the first go around of the loop but don't perform the expected task in subsequent loops.
My goal is to copy and transpose data from a single column several thousand cells tall into a new location 22 cells at a time. So my loop is meant to copy H2:H23, then move down and copy H24:H46, and so on. Each time it transposes and pastes the data into a new location one cell lower than the most recent pasting. When it runs the way it is now I find that the code makes a single copy and I see it selecting the next range down but I can't figure out how to make it remember that range and continue to transpose the rest of the row.
If you can figure out how to make this work I would be forever grateful!
Sub TranspositionLoop()
Dim SourceRange As Range
Dim DestRange As Range
Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
For x = 1 To 257
SourceRange.Select
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
SourceRange.Offset(23, 0).Select
DestRange.Offset(1, 0).Select
Next x
End Sub
excel vba excel-vba
asked Jan 4 at 18:30
AdornsyAdornsy
64
marked as duplicate by GSerg excel
Users with the excel badge can single-handedly close excel questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 4 at 18:48
This question was marked as an exact duplicate of an existing question.
add a comment |
This question is an exact duplicate of:
Transpose static range in a loop
1 answer
Thanks for taking the time to read my post. I am attempting to write my first VBA macro and am getting myself confused about what to do next. I found a few pieces of code online that I cobbled together, which work perfectly on the first go around of the loop but don't perform the expected task in subsequent loops.
My goal is to copy and transpose data from a single column several thousand cells tall into a new location 22 cells at a time. So my loop is meant to copy H2:H23, then move down and copy H24:H46, and so on. Each time it transposes and pastes the data into a new location one cell lower than the most recent pasting. When it runs the way it is now I find that the code makes a single copy and I see it selecting the next range down but I can't figure out how to make it remember that range and continue to transpose the rest of the row.
If you can figure out how to make this work I would be forever grateful!
Sub TranspositionLoop()
Dim SourceRange As Range
Dim DestRange As Range
Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
For x = 1 To 257
SourceRange.Select
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
SourceRange.Offset(23, 0).Select
DestRange.Offset(1, 0).Select
Next x
End Sub
excel vba excel-vba
asked Jan 4 at 18:30
AdornsyAdornsy
64
marked as duplicate by GSerg excel
Users with the excel badge can single-handedly close excel questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 4 at 18:48
This question was marked as an exact duplicate of an existing question.
3
Does stackoverflow.com/a/21129568/11683 solve your problem?
– GSerg
Jan 4 at 18:37
2
You really want to avoid the use of Select in your code. it will not always result in the cell you expect being selected.
– cybernetic.nomad
Jan 4 at 18:37
add a comment |
This question is an exact duplicate of:
Transpose static range in a loop
1 answer
Thanks for taking the time to read my post. I am attempting to write my first VBA macro and am getting myself confused about what to do next. I found a few pieces of code online that I cobbled together, which work perfectly on the first go around of the loop but don't perform the expected task in subsequent loops.
My goal is to copy and transpose data from a single column several thousand cells tall into a new location 22 cells at a time. So my loop is meant to copy H2:H23, then move down and copy H24:H46, and so on. Each time it transposes and pastes the data into a new location one cell lower than the most recent pasting. When it runs the way it is now I find that the code makes a single copy and I see it selecting the next range down but I can't figure out how to make it remember that range and continue to transpose the rest of the row.
If you can figure out how to make this work I would be forever grateful!
Sub TranspositionLoop()
Dim SourceRange As Range
Dim DestRange As Range
Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
For x = 1 To 257
SourceRange.Select
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
SourceRange.Offset(23, 0).Select
DestRange.Offset(1, 0).Select
Next x
End Sub
excel vba excel-vba
asked Jan 4 at 18:30
AdornsyAdornsy
64
This question is an exact duplicate of:
Transpose static range in a loop
1 answer
Thanks for taking the time to read my post. I am attempting to write my first VBA macro and am getting myself confused about what to do next. I found a few pieces of code online that I cobbled together, which work perfectly on the first go around of the loop but don't perform the expected task in subsequent loops.
My goal is to copy and transpose data from a single column several thousand cells tall into a new location 22 cells at a time. So my loop is meant to copy H2:H23, then move down and copy H24:H46, and so on. Each time it transposes and pastes the data into a new location one cell lower than the most recent pasting. When it runs the way it is now I find that the code makes a single copy and I see it selecting the next range down but I can't figure out how to make it remember that range and continue to transpose the rest of the row.
If you can figure out how to make this work I would be forever grateful!
Sub TranspositionLoop()
Dim SourceRange As Range
Dim DestRange As Range
Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
For x = 1 To 257
SourceRange.Select
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
SourceRange.Offset(23, 0).Select
DestRange.Offset(1, 0).Select
Next x
End Sub
This question is an exact duplicate of:
Transpose static range in a loop
1 answer
excel vba excel-vba
excel vba excel-vba
asked Jan 4 at 18:30
AdornsyAdornsy
64
asked Jan 4 at 18:30
AdornsyAdornsy
64
edited Jan 4 at 18:39
Adornsy
asked Jan 4 at 18:30
AdornsyAdornsy
64
asked Jan 4 at 18:30
AdornsyAdornsy
64
asked Jan 4 at 18:30
AdornsyAdornsy
64
64
marked as duplicate by GSerg excel
Users with the excel badge can single-handedly close excel questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 4 at 18:48
This question was marked as an exact duplicate of an existing question.
marked as duplicate by GSerg excel
Users with the excel badge can single-handedly close excel questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 4 at 18:48
This question was marked as an exact duplicate of an existing question.
3
Does stackoverflow.com/a/21129568/11683 solve your problem?
– GSerg
Jan 4 at 18:37
2
You really want to avoid the use of Select in your code. it will not always result in the cell you expect being selected.
– cybernetic.nomad
Jan 4 at 18:37
add a comment |
3
Does stackoverflow.com/a/21129568/11683 solve your problem?
– GSerg
Jan 4 at 18:37
2
You really want to avoid the use of Select in your code. it will not always result in the cell you expect being selected.
– cybernetic.nomad
Jan 4 at 18:37
3
3
Does stackoverflow.com/a/21129568/11683 solve your problem?
– GSerg
Jan 4 at 18:37
Does stackoverflow.com/a/21129568/11683 solve your problem?
– GSerg
Jan 4 at 18:37
2
2
You really want to avoid the use of Select in your code. it will not always result in the cell you expect being selected.
– cybernetic.nomad
Jan 4 at 18:37
You really want to avoid the use of Select in your code. it will not always result in the cell you expect being selected.
– cybernetic.nomad
Jan 4 at 18:37
add a comment |
1 Answer
1
active
oldest
votes
I figured it out.
Sub TranspositionLoop()
Dim SourceRange As Range
Dim DestRange As Range
Dim x As Integer
Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
For x = 1 To 5
SourceRange.Select
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Set SourceRange = SourceRange.Offset(22, 0)
Set DestRange = DestRange.Offset(1, 0)
Next x
End Sub
answered Jan 4 at 18:46
AdornsyAdornsy
64
@Adomsy: Nice. Try Transpose Column to Range.
– VBasic2008
Jan 4 at 20:41
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I figured it out.
Sub TranspositionLoop()
Dim SourceRange As Range
Dim DestRange As Range
Dim x As Integer
Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
For x = 1 To 5
SourceRange.Select
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Set SourceRange = SourceRange.Offset(22, 0)
Set DestRange = DestRange.Offset(1, 0)
Next x
End Sub
answered Jan 4 at 18:46
AdornsyAdornsy
64
@Adomsy: Nice. Try Transpose Column to Range.
– VBasic2008
Jan 4 at 20:41
add a comment |
I figured it out.
Sub TranspositionLoop()
Dim SourceRange As Range
Dim DestRange As Range
Dim x As Integer
Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
For x = 1 To 5
SourceRange.Select
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Set SourceRange = SourceRange.Offset(22, 0)
Set DestRange = DestRange.Offset(1, 0)
Next x
End Sub
answered Jan 4 at 18:46
AdornsyAdornsy
64
@Adomsy: Nice. Try Transpose Column to Range.
– VBasic2008
Jan 4 at 20:41
add a comment |
I figured it out.
Sub TranspositionLoop()
Dim SourceRange As Range
Dim DestRange As Range
Dim x As Integer
Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
For x = 1 To 5
SourceRange.Select
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Set SourceRange = SourceRange.Offset(22, 0)
Set DestRange = DestRange.Offset(1, 0)
Next x
End Sub
answered Jan 4 at 18:46
AdornsyAdornsy
64
I figured it out.
Sub TranspositionLoop()
Dim SourceRange As Range
Dim DestRange As Range
Dim x As Integer
Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)
For x = 1 To 5
SourceRange.Select
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Set SourceRange = SourceRange.Offset(22, 0)
Set DestRange = DestRange.Offset(1, 0)
Next x
End Sub
answered Jan 4 at 18:46
AdornsyAdornsy
64
answered Jan 4 at 18:46
AdornsyAdornsy
64
answered Jan 4 at 18:46
AdornsyAdornsy
64
answered Jan 4 at 18:46
AdornsyAdornsy
64
64
@Adomsy: Nice. Try Transpose Column to Range.
– VBasic2008
Jan 4 at 20:41
add a comment |
@Adomsy: Nice. Try Transpose Column to Range.
– VBasic2008
Jan 4 at 20:41
@Adomsy: Nice. Try Transpose Column to Range.
– VBasic2008
Jan 4 at 20:41
@Adomsy: Nice. Try Transpose Column to Range.
– VBasic2008
Jan 4 at 20:41
add a comment |
3
Does stackoverflow.com/a/21129568/11683 solve your problem?
– GSerg
Jan 4 at 18:37
2
You really want to avoid the use of Select in your code. it will not always result in the cell you expect being selected.
– cybernetic.nomad
Jan 4 at 18:37