ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Transpose paste with space (https://www.excelbanter.com/excel-programming/412876-transpose-paste-space.html)

Chance224
Transpose paste with space
 
I recorded a macro that copies a list in column AN2:AN60 and then transposes
the list into AO1 (across the row). Is there a way to have it paste the list
in every other cell?
It would paste it Ao1= First value, skip AP1, AQ1= next value, skip
AR1,AS1=next value, etc€¦
Here is the code I have so far:
Sub Jobs_Across()
'
Range("AN2:AN60").Select
Selection.Copy
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

Thanks

Nayab
Transpose paste with space
 
On Jun 20, 7:20*pm, Chance224
wrote:
I recorded a macro that copies a list in column AN2:AN60 and then transposes
the list into AO1 (across the row). Is there a way to have it paste the list
in every other cell?
It would paste it Ao1= First value, skip AP1, AQ1= next value, skip
AR1,AS1=next value, etc…
Here is the code I have so far:
Sub Jobs_Across()
'
* * Range("AN2:AN60").Select
* * Selection.Copy
* * Range("AO1").Select
* * Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
* * * * False, Transpose:=True
End Sub

Thanks

May b this can help:::::::::::

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/20/2008 by Nayab
'

'
Dim r As Range
Dim i, j As Integer
Range("A1:A7").Select
i = 0
j = 0

For Each r In Selection
r.Select
r.Copy
Selection.Offset(i, j + 1).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
ActiveWindow.SmallScroll ToRight:=3
i = i - 1
j = j + 2
Next r
End Sub

Rick Rothstein \(MVP - VB\)[_2155_]
Transpose paste with space
 
Instead Copy/PasteSpecial, what about doing it with a simple loop?

Sub Jobs_Across()
Dim X As Long
For X = 2 To 60
Cells(1, 41 + 2 * (X - 2)).Value = Cells(X, "AN").Value
Next
End Sub

If you want to generalize this so you can play around with different skip
values, and source/destination ranges...

Sub Jobs_Across()
Dim X As Long
Const SkipValue As Long = 3
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 40 ' Column AO
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AN
For X = SourceStartRow To SourceEndRow
Cells(DestinationStartRow, DestinationColumn + SkipValue * _
(X - SourceStartRow)).Value = Cells(X, SourceColumn).Value
Next
End Sub


Rick


"Chance224" wrote in message
...
I recorded a macro that copies a list in column AN2:AN60 and then
transposes
the list into AO1 (across the row). Is there a way to have it paste the
list
in every other cell?
It would paste it Ao1= First value, skip AP1, AQ1= next value, skip
AR1,AS1=next value, etc€¦
Here is the code I have so far:
Sub Jobs_Across()
'
Range("AN2:AN60").Select
Selection.Copy
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

Thanks

Chance224
Transpose paste with space
 
Thanks this worked great. I have decided to change the source column to
another sheet. It is located on Sheet3 A2:60. I tried to play around with the
code to get it to work but I can't get it to paste with the spaces on Sheet1
starting at AO1. Any additional help would be greatly appreciated.

Thanks,
Chance

"Nayab" wrote:

On Jun 20, 7:20 pm, Chance224
wrote:
I recorded a macro that copies a list in column AN2:AN60 and then transposes
the list into AO1 (across the row). Is there a way to have it paste the list
in every other cell?
It would paste it Ao1= First value, skip AP1, AQ1= next value, skip
AR1,AS1=next value, etc€¦
Here is the code I have so far:
Sub Jobs_Across()
'
Range("AN2:AN60").Select
Selection.Copy
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

Thanks

May b this can help:::::::::::

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/20/2008 by Nayab
'

'
Dim r As Range
Dim i, j As Integer
Range("A1:A7").Select
i = 0
j = 0

For Each r In Selection
r.Select
r.Copy
Selection.Offset(i, j + 1).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
ActiveWindow.SmallScroll ToRight:=3
i = i - 1
j = j + 2
Next r
End Sub

Chance224
Transpose paste with space
 
Thanks this worked great and seems to be a faster approach. I have decided to
change the source column to another sheet. It is located on Sheet3 A2:60. I
tried to play around with the code to get it to work but I can't get it to
paste with the spaces on Sheet1 starting at AO1. Any additional help would be
greatly appreciated.


"Rick Rothstein (MVP - VB)" wrote:

Instead Copy/PasteSpecial, what about doing it with a simple loop?

Sub Jobs_Across()
Dim X As Long
For X = 2 To 60
Cells(1, 41 + 2 * (X - 2)).Value = Cells(X, "AN").Value
Next
End Sub

If you want to generalize this so you can play around with different skip
values, and source/destination ranges...

Sub Jobs_Across()
Dim X As Long
Const SkipValue As Long = 3
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 40 ' Column AO
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AN
For X = SourceStartRow To SourceEndRow
Cells(DestinationStartRow, DestinationColumn + SkipValue * _
(X - SourceStartRow)).Value = Cells(X, SourceColumn).Value
Next
End Sub


Rick


"Chance224" wrote in message
...
I recorded a macro that copies a list in column AN2:AN60 and then
transposes
the list into AO1 (across the row). Is there a way to have it paste the
list
in every other cell?
It would paste it Ao1= First value, skip AP1, AQ1= next value, skip
AR1,AS1=next value, etc€¦
Here is the code I have so far:
Sub Jobs_Across()
'
Range("AN2:AN60").Select
Selection.Copy
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

Thanks

Rick Rothstein \(MVP - VB\)[_2172_]
Transpose paste with space
 
Here is my generalized code modified for the worksheets** and ranges you now
want to use.

Sub Jobs_Across()
Dim X As Long
' Set number of spaces to skip in the transpose
Const SkipValue As Long = 2
' Set Source Information Here
Const SourceSheet As String = "Sheet3"
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 1 ' Column A
' Set Destination Information Here
Const DestinationSheet As String = "Sheet1"
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AO
' Loop to transpose the Source information to the Destination
For X = SourceStartRow To SourceEndRow
Worksheets(DestinationSheet).Cells(DestinationStar tRow, _
DestinationColumn + SkipValue * (X - SourceStartRow)).Value = _
Worksheets(SourceSheet).Cells(X, SourceColumn).Value
Next
End Sub

** Note that I added two more Const statements where you can now set the
source and destination worksheets. As I said, the Const statements are set
for your new conditions. If you wish to change anything else, simply set the
indicated Const statements for the required Source and Destination locations
(as well as the Skip amount) and the For-Next loop will take care of the
rest.

Rick


"Chance224" wrote in message
...
Thanks this worked great and seems to be a faster approach. I have decided
to
change the source column to another sheet. It is located on Sheet3 A2:60.
I
tried to play around with the code to get it to work but I can't get it to
paste with the spaces on Sheet1 starting at AO1. Any additional help would
be
greatly appreciated.


"Rick Rothstein (MVP - VB)" wrote:

Instead Copy/PasteSpecial, what about doing it with a simple loop?

Sub Jobs_Across()
Dim X As Long
For X = 2 To 60
Cells(1, 41 + 2 * (X - 2)).Value = Cells(X, "AN").Value
Next
End Sub

If you want to generalize this so you can play around with different skip
values, and source/destination ranges...

Sub Jobs_Across()
Dim X As Long
Const SkipValue As Long = 3
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 40 ' Column AO
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AN
For X = SourceStartRow To SourceEndRow
Cells(DestinationStartRow, DestinationColumn + SkipValue * _
(X - SourceStartRow)).Value = Cells(X, SourceColumn).Value
Next
End Sub


Rick


"Chance224" wrote in message
...
I recorded a macro that copies a list in column AN2:AN60 and then
transposes
the list into AO1 (across the row). Is there a way to have it paste the
list
in every other cell?
It would paste it Ao1= First value, skip AP1, AQ1= next value, skip
AR1,AS1=next value, etc€¦
Here is the code I have so far:
Sub Jobs_Across()
'
Range("AN2:AN60").Select
Selection.Copy
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

Thanks

Chance224
Transpose paste with space
 
Thank you!

"Rick Rothstein (MVP - VB)" wrote:

Here is my generalized code modified for the worksheets** and ranges you now
want to use.

Sub Jobs_Across()
Dim X As Long
' Set number of spaces to skip in the transpose
Const SkipValue As Long = 2
' Set Source Information Here
Const SourceSheet As String = "Sheet3"
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 1 ' Column A
' Set Destination Information Here
Const DestinationSheet As String = "Sheet1"
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AO
' Loop to transpose the Source information to the Destination
For X = SourceStartRow To SourceEndRow
Worksheets(DestinationSheet).Cells(DestinationStar tRow, _
DestinationColumn + SkipValue * (X - SourceStartRow)).Value = _
Worksheets(SourceSheet).Cells(X, SourceColumn).Value
Next
End Sub

** Note that I added two more Const statements where you can now set the
source and destination worksheets. As I said, the Const statements are set
for your new conditions. If you wish to change anything else, simply set the
indicated Const statements for the required Source and Destination locations
(as well as the Skip amount) and the For-Next loop will take care of the
rest.

Rick


"Chance224" wrote in message
...
Thanks this worked great and seems to be a faster approach. I have decided
to
change the source column to another sheet. It is located on Sheet3 A2:60.
I
tried to play around with the code to get it to work but I can't get it to
paste with the spaces on Sheet1 starting at AO1. Any additional help would
be
greatly appreciated.


"Rick Rothstein (MVP - VB)" wrote:

Instead Copy/PasteSpecial, what about doing it with a simple loop?

Sub Jobs_Across()
Dim X As Long
For X = 2 To 60
Cells(1, 41 + 2 * (X - 2)).Value = Cells(X, "AN").Value
Next
End Sub

If you want to generalize this so you can play around with different skip
values, and source/destination ranges...

Sub Jobs_Across()
Dim X As Long
Const SkipValue As Long = 3
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 40 ' Column AO
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AN
For X = SourceStartRow To SourceEndRow
Cells(DestinationStartRow, DestinationColumn + SkipValue * _
(X - SourceStartRow)).Value = Cells(X, SourceColumn).Value
Next
End Sub


Rick


"Chance224" wrote in message
...
I recorded a macro that copies a list in column AN2:AN60 and then
transposes
the list into AO1 (across the row). Is there a way to have it paste the
list
in every other cell?
It would paste it Ao1= First value, skip AP1, AQ1= next value, skip
AR1,AS1=next value, etc€¦
Here is the code I have so far:
Sub Jobs_Across()
'
Range("AN2:AN60").Select
Selection.Copy
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

Thanks

Brian Cox
Change code to paste down instead of across?
 
I tried several variations to Rick's last code to be able to copy a range and paste it down a column with skipped cells, but couldn't figure it out.

Is there a simple switch to allow this to work for the destination going down a column?

Thanks!!



Rick Rothstein \(MVP - VB\) wrote:

Here is my generalized code modified for the worksheets** and ranges you now
23-Jun-08

Here is my generalized code modified for the worksheets** and ranges you now
want to use.

Sub Jobs_Across()
Dim X As Long
' Set number of spaces to skip in the transpose
Const SkipValue As Long = 2
' Set Source Information Here
Const SourceSheet As String = "Sheet3"
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 1 ' Column A
' Set Destination Information Here
Const DestinationSheet As String = "Sheet1"
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AO
' Loop to transpose the Source information to the Destination
For X = SourceStartRow To SourceEndRow
Worksheets(DestinationSheet).Cells(DestinationStar tRow, _
DestinationColumn + SkipValue * (X - SourceStartRow)).Value = _
Worksheets(SourceSheet).Cells(X, SourceColumn).Value
Next
End Sub

** Note that I added two more Const statements where you can now set the
source and destination worksheets. As I said, the Const statements are set
for your new conditions. If you wish to change anything else, simply set the
indicated Const statements for the required Source and Destination locations
(as well as the Skip amount) and the For-Next loop will take care of the
rest.

Rick


"Chance224" wrote in message
...

Previous Posts In This Thread:

On Friday, June 20, 2008 10:20 AM
Chance22 wrote:

Transpose paste with space
I recorded a macro that copies a list in column AN2:AN60 and then transposes
the list into AO1 (across the row). Is there a way to have it paste the list
in every other cell?
It would paste it Ao1= First value, skip AP1, AQ1= next value, skip
AR1,AS1=next value, etc???
Here is the code I have so far:
Sub Jobs_Across()
'
Range("AN2:AN60").Select
Selection.Copy
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

Thanks

On Friday, June 20, 2008 11:44 AM
Rick Rothstein \(MVP - VB\) wrote:

Instead Copy/PasteSpecial, what about doing it with a simple loop?
Instead Copy/PasteSpecial, what about doing it with a simple loop?

Sub Jobs_Across()
Dim X As Long
For X = 2 To 60
Cells(1, 41 + 2 * (X - 2)).Value = Cells(X, "AN").Value
Next
End Sub

If you want to generalize this so you can play around with different skip
values, and source/destination ranges...

Sub Jobs_Across()
Dim X As Long
Const SkipValue As Long = 3
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 40 ' Column AO
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AN
For X = SourceStartRow To SourceEndRow
Cells(DestinationStartRow, DestinationColumn + SkipValue * _
(X - SourceStartRow)).Value = Cells(X, SourceColumn).Value
Next
End Sub


Rick


"Chance224" wrote in message
...

On Friday, June 20, 2008 9:12 PM
Nayab wrote:

Transpose paste with space
On Jun 20, 7:20=A0pm, Chance224
wrote:
ses
ist

May b this can help:::::::::::

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/20/2008 by Nayab
'

'
Dim r As Range
Dim i, j As Integer
Range("A1:A7").Select
i =3D 0
j =3D 0

For Each r In Selection
r.Select
r.Copy
Selection.Offset(i, j + 1).PasteSpecial
Paste:=3DxlPasteValuesAndNumberFormats, Operation:=3D _
xlNone, SkipBlanks:=3DFalse, Transpose:=3DTrue
ActiveWindow.SmallScroll ToRight:=3D3
i =3D i - 1
j =3D j + 2
Next r
End Sub

On Monday, June 23, 2008 10:29 AM
Chance22 wrote:

Thanks this worked great.
Thanks this worked great. I have decided to change the source column to
another sheet. It is located on Sheet3 A2:60. I tried to play around with the
code to get it to work but I can't get it to paste with the spaces on Sheet1
starting at AO1. Any additional help would be greatly appreciated.

Thanks,
Chance

"Nayab" wrote:

On Monday, June 23, 2008 11:32 AM
Chance22 wrote:

Thanks this worked great and seems to be a faster approach.
Thanks this worked great and seems to be a faster approach. I have decided to
change the source column to another sheet. It is located on Sheet3 A2:60. I
tried to play around with the code to get it to work but I can't get it to
paste with the spaces on Sheet1 starting at AO1. Any additional help would be
greatly appreciated.


"Rick Rothstein (MVP - VB)" wrote:

On Monday, June 23, 2008 12:39 PM
Rick Rothstein \(MVP - VB\) wrote:

Here is my generalized code modified for the worksheets** and ranges you now
Here is my generalized code modified for the worksheets** and ranges you now
want to use.

Sub Jobs_Across()
Dim X As Long
' Set number of spaces to skip in the transpose
Const SkipValue As Long = 2
' Set Source Information Here
Const SourceSheet As String = "Sheet3"
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 1 ' Column A
' Set Destination Information Here
Const DestinationSheet As String = "Sheet1"
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AO
' Loop to transpose the Source information to the Destination
For X = SourceStartRow To SourceEndRow
Worksheets(DestinationSheet).Cells(DestinationStar tRow, _
DestinationColumn + SkipValue * (X - SourceStartRow)).Value = _
Worksheets(SourceSheet).Cells(X, SourceColumn).Value
Next
End Sub

** Note that I added two more Const statements where you can now set the
source and destination worksheets. As I said, the Const statements are set
for your new conditions. If you wish to change anything else, simply set the
indicated Const statements for the required Source and Destination locations
(as well as the Skip amount) and the For-Next loop will take care of the
rest.

Rick


"Chance224" wrote in message
...

On Monday, June 23, 2008 12:59 PM
Chance22 wrote:

Transpose paste with space
Thank you!

"Rick Rothstein (MVP - VB)" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Using Reflection to Create a Data Access Class
http://www.eggheadcafe.com/tutorials...-to-creat.aspx

Hans
Change code to paste down instead of across?
 
Amend following lines:
Worksheets(DestinationSheet).Cells(DestinationStar tRow, _
DestinationColumn + SkipValue * (X - SourceStartRow)).Value = _
Worksheets(SourceSheet).Cells(X, SourceColumn).Value

to this:
Worksheets(DestinationSheet).Cells(DestinationStar tRow + SkipValue * (X - SourceStartRow), _
DestinationColumn).Value = _
Worksheets(SourceSheet).Cells(X, SourceColumn).Value

That pastes in DestinationColumn, starting in DestinationStartRow and skipping cells in that column per SkipValue.



Brian Cox wrote:

Change code to paste down instead of across?
28-Jan-10

I tried several variations to Rick's last code to be able to copy a range and paste it down a column with skipped cells, but couldn't figure it out.

Is there a simple switch to allow this to work for the destination going down a column?

Thanks!!

Previous Posts In This Thread:

On Friday, June 20, 2008 10:20 AM
Chance22 wrote:

Transpose paste with space
I recorded a macro that copies a list in column AN2:AN60 and then transposes
the list into AO1 (across the row). Is there a way to have it paste the list
in every other cell?
It would paste it Ao1= First value, skip AP1, AQ1= next value, skip
AR1,AS1=next value, etc???
Here is the code I have so far:
Sub Jobs_Across()
'
Range("AN2:AN60").Select
Selection.Copy
Range("AO1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

Thanks

On Friday, June 20, 2008 11:44 AM
Rick Rothstein \(MVP - VB\) wrote:

Instead Copy/PasteSpecial, what about doing it with a simple loop?
Instead Copy/PasteSpecial, what about doing it with a simple loop?

Sub Jobs_Across()
Dim X As Long
For X = 2 To 60
Cells(1, 41 + 2 * (X - 2)).Value = Cells(X, "AN").Value
Next
End Sub

If you want to generalize this so you can play around with different skip
values, and source/destination ranges...

Sub Jobs_Across()
Dim X As Long
Const SkipValue As Long = 3
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 40 ' Column AO
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AN
For X = SourceStartRow To SourceEndRow
Cells(DestinationStartRow, DestinationColumn + SkipValue * _
(X - SourceStartRow)).Value = Cells(X, SourceColumn).Value
Next
End Sub


Rick


"Chance224" wrote in message
...

On Friday, June 20, 2008 9:12 PM
Nayab wrote:

Transpose paste with space
On Jun 20, 7:20=A0pm, Chance224
wrote:
ses
ist

May b this can help:::::::::::

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/20/2008 by Nayab
'

'
Dim r As Range
Dim i, j As Integer
Range("A1:A7").Select
i =3D 0
j =3D 0

For Each r In Selection
r.Select
r.Copy
Selection.Offset(i, j + 1).PasteSpecial
Paste:=3DxlPasteValuesAndNumberFormats, Operation:=3D _
xlNone, SkipBlanks:=3DFalse, Transpose:=3DTrue
ActiveWindow.SmallScroll ToRight:=3D3
i =3D i - 1
j =3D j + 2
Next r
End Sub

On Monday, June 23, 2008 10:29 AM
Chance22 wrote:

Thanks this worked great.
Thanks this worked great. I have decided to change the source column to
another sheet. It is located on Sheet3 A2:60. I tried to play around with the
code to get it to work but I can't get it to paste with the spaces on Sheet1
starting at AO1. Any additional help would be greatly appreciated.

Thanks,
Chance

"Nayab" wrote:

On Monday, June 23, 2008 11:32 AM
Chance22 wrote:

Thanks this worked great and seems to be a faster approach.
Thanks this worked great and seems to be a faster approach. I have decided to
change the source column to another sheet. It is located on Sheet3 A2:60. I
tried to play around with the code to get it to work but I can't get it to
paste with the spaces on Sheet1 starting at AO1. Any additional help would be
greatly appreciated.


"Rick Rothstein (MVP - VB)" wrote:

On Monday, June 23, 2008 12:39 PM
Rick Rothstein \(MVP - VB\) wrote:

Here is my generalized code modified for the worksheets** and ranges you now
Here is my generalized code modified for the worksheets** and ranges you now
want to use.

Sub Jobs_Across()
Dim X As Long
' Set number of spaces to skip in the transpose
Const SkipValue As Long = 2
' Set Source Information Here
Const SourceSheet As String = "Sheet3"
Const SourceStartRow As Long = 2
Const SourceEndRow As Long = 60
Const SourceColumn As Long = 1 ' Column A
' Set Destination Information Here
Const DestinationSheet As String = "Sheet1"
Const DestinationStartRow As Long = 1
Const DestinationColumn As Long = 41 ' Column AO
' Loop to transpose the Source information to the Destination
For X = SourceStartRow To SourceEndRow
Worksheets(DestinationSheet).Cells(DestinationStar tRow, _
DestinationColumn + SkipValue * (X - SourceStartRow)).Value = _
Worksheets(SourceSheet).Cells(X, SourceColumn).Value
Next
End Sub

** Note that I added two more Const statements where you can now set the
source and destination worksheets. As I said, the Const statements are set
for your new conditions. If you wish to change anything else, simply set the
indicated Const statements for the required Source and Destination locations
(as well as the Skip amount) and the For-Next loop will take care of the
rest.

Rick


"Chance224" wrote in message
...

On Monday, June 23, 2008 12:59 PM
Chance22 wrote:

Transpose paste with space
Thank you!

"Rick Rothstein (MVP - VB)" wrote:

On Thursday, January 28, 2010 12:14 PM
Brian Cox wrote:

Change code to paste down instead of across?
I tried several variations to Rick's last code to be able to copy a range and paste it down a column with skipped cells, but couldn't figure it out.

Is there a simple switch to allow this to work for the destination going down a column?

Thanks!!


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Video Library Template Available For Download
http://www.eggheadcafe.com/tutorials...o-library.aspx


All times are GMT +1. The time now is 04:37 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com