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(DestinationStartRow, _
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
Previous Posts In This Thread:
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
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
Re: Transpose paste with space
On Jun 20, 7:20=A0pm, Chance224 <
[email protected]>
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
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
:
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.
:
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(DestinationStartRow, _
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
Re: Transpose paste with space
Thank you!
:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Using Reflection to Create a Data Access Class
http://www.eggheadcafe.com/tutorial...2-134b46b44f20/using-reflection-to-creat.aspx