Moving down one row in a macro

[Cathy S.]

I am an inexperienced Excel user. I have a report in Excel using A1 through
A36000. I recorded the following macro. The macro works, but when I run it,
the new information uses the same row, covering up the previous information.
How can I get it to move down one row each time. (I want a1 through a20 to
go across a row at c1, then a21 through a40 to appear on the next row (c2),
etc.) If there is a way to get a macro to run through all 36000 cells at one
time, instead of running this macro over and over--that would be great too!

Sub Transpose()
'
' Transpose Macro
' Macro recorded 1/4/2005 by cstokley
'
' Keyboard Shortcut: Ctrl+t
'
Range("A1:A20").Select
Selection.Copy
Range("C1").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Range("A1:A20").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("C2").Select
End Sub

--
Thanks in advance for your help,
Cathy S.

hi,
try this
Sub macMoveUp() 'start
Dim rng1 As Range 'declare a variable type
Dim cng1 As Range 'declare a variable type
Dim cng2 As Range 'declare a variable type
Set rng1 = Range("A1")'assign a range to variable
Set cng1 = Range("C1")'assign a range to variable
Do While Not IsEmpty(rng1) 'set loop and a way out
Set cng2 = cng1.Offset(1, 0) 'set up for drop down
Range(rng1, rng1.Offset(19, 0))'Select range
Selection.Copy 'copy range
cng1.Select 'select destination
Selection.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlnone, SkipBlanks:=False, Transpose:=True
' paste transposed
Range(rng1, rng1.Offset(19, 0)).Select 'reselect range
Application.CutCopyMode = False 'clear clipboard
Selection.Delete Shift:=xlUp 'delete range
Set cng1 = cng2 'drop down one row
Set rng1 = Range("A1") 're set
Loop 'do again
msgbox("Macro complete") 'message stating macro done
End Sub 'stop

>-----Original Message-----
>I am an inexperienced Excel user. I have a report in
Excel using A1 through
>A36000. I recorded the following macro. The macro
works, but when I run it,
>the new information uses the same row, covering up the
previous information.
>How can I get it to move down one row each time. (I want
a1 through a20 to
>go across a row at c1, then a21 through a40 to appear on
the next row (c2),
>etc.) If there is a way to get a macro to run through
all 36000 cells at one
>time, instead of running this macro over and over--that
would be great too!
>
>Sub Transpose()
>'
>' Transpose Macro
>' Macro recorded 1/4/2005 by cstokley
>'
>' Keyboard Shortcut: Ctrl+t
>'
> Range("A1:A20").Select
> Selection.Copy
> Range("C1").Select
> Range("C1").Select
> Selection.PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone,
>SkipBlanks:= _
> False, Transpose:=True
> Range("A1:A20").Select
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlUp
> Range("C2").Select
>End Sub
>
>--
>Thanks in advance for your help,
>Cathy S.
>.
>

[Cathy S.]

Sorry--please tell me what to do with this. I'm a beginner! Thank you for
your time.

"[email protected]" wrote:

> hi,
> try this
> Sub macMoveUp() 'start
> Dim rng1 As Range 'declare a variable type
> Dim cng1 As Range 'declare a variable type
> Dim cng2 As Range 'declare a variable type
> Set rng1 = Range("A1")'assign a range to variable
> Set cng1 = Range("C1")'assign a range to variable
> Do While Not IsEmpty(rng1) 'set loop and a way out
> Set cng2 = cng1.Offset(1, 0) 'set up for drop down
> Range(rng1, rng1.Offset(19, 0))'Select range
> Selection.Copy 'copy range
> cng1.Select 'select destination
> Selection.PasteSpecial Paste:=xlPasteAll, _
> Operation:=xlnone, SkipBlanks:=False, Transpose:=True
> ' paste transposed
> Range(rng1, rng1.Offset(19, 0)).Select 'reselect range
> Application.CutCopyMode = False 'clear clipboard
> Selection.Delete Shift:=xlUp 'delete range
> Set cng1 = cng2 'drop down one row
> Set rng1 = Range("A1") 're set
> Loop 'do again
> msgbox("Macro complete") 'message stating macro done
> End Sub 'stop
>
> >-----Original Message-----
> >I am an inexperienced Excel user. I have a report in
> Excel using A1 through
> >A36000. I recorded the following macro. The macro
> works, but when I run it,
> >the new information uses the same row, covering up the
> previous information.
> >How can I get it to move down one row each time. (I want
> a1 through a20 to
> >go across a row at c1, then a21 through a40 to appear on
> the next row (c2),
> >etc.) If there is a way to get a macro to run through
> all 36000 cells at one
> >time, instead of running this macro over and over--that
> would be great too!
> >
> >Sub Transpose()
> >'
> >' Transpose Macro
> >' Macro recorded 1/4/2005 by cstokley
> >'
> >' Keyboard Shortcut: Ctrl+t
> >'
> > Range("A1:A20").Select
> > Selection.Copy
> > Range("C1").Select
> > Range("C1").Select
> > Selection.PasteSpecial Paste:=xlPasteAll,
> Operation:=xlNone,
> >SkipBlanks:= _
> > False, Transpose:=True
> > Range("A1:A20").Select
> > Application.CutCopyMode = False
> > Selection.Delete Shift:=xlUp
> > Range("C2").Select
> >End Sub
> >
> >--
> >Thanks in advance for your help,
> >Cathy S.
> >.
> >
>

hi again,
sorry about that.
tools>macro>macros or alt+F8
enter a macro name in the name box then click create.
the vb editor will come up.
copy all of the code below EXCEPT:
Sub macMoveUp()
End Sub
paste the copied code in to the vb editor between the sub
and end sub.
you might experience some problem with the paste part as
is does not seem to be wraping properly.
it should be 2 lines with a underscore _ at the end of the
first line.
hope this helps.
I am leaving work now so good luck. i will check is post
tomorrow if you have any more problems.

>-----Original Message-----
>Sorry--please tell me what to do with this. I'm a
beginner! Thank you for
>your time.
>
>"[email protected]" wrote:
>
>> hi,
>> try this
>> Sub macMoveUp() 'start
>> Dim rng1 As Range 'declare a variable type
>> Dim cng1 As Range 'declare a variable type
>> Dim cng2 As Range 'declare a variable type
>> Set rng1 = Range("A1")'assign a range to variable
>> Set cng1 = Range("C1")'assign a range to variable
>> Do While Not IsEmpty(rng1) 'set loop and a way out
>> Set cng2 = cng1.Offset(1, 0) 'set up for drop down
>> Range(rng1, rng1.Offset(19, 0))'Select range
>> Selection.Copy 'copy range
>> cng1.Select 'select destination
>> Selection.PasteSpecial Paste:=xlPasteAll,
_
>> Operation:=xlnone, SkipBlanks:=False,
Transpose:=True
>> ' paste transposed
>> Range(rng1, rng1.Offset(19, 0)).Select 'reselect
range
>> Application.CutCopyMode = False 'clear clipboard
>> Selection.Delete Shift:=xlUp 'delete range
>> Set cng1 = cng2 'drop down one row
>> Set rng1 = Range("A1") 're set
>> Loop 'do again
>> msgbox("Macro complete") 'message stating macro done
>> End Sub 'stop
>>
>> >-----Original Message-----
>> >I am an inexperienced Excel user. I have a report in
>> Excel using A1 through
>> >A36000. I recorded the following macro. The macro
>> works, but when I run it,
>> >the new information uses the same row, covering up the
>> previous information.
>> >How can I get it to move down one row each time. (I
want
>> a1 through a20 to
>> >go across a row at c1, then a21 through a40 to appear
on
>> the next row (c2),
>> >etc.) If there is a way to get a macro to run through
>> all 36000 cells at one
>> >time, instead of running this macro over and over--
that
>> would be great too!
>> >
>> >Sub Transpose()
>> >'
>> >' Transpose Macro
>> >' Macro recorded 1/4/2005 by cstokley
>> >'
>> >' Keyboard Shortcut: Ctrl+t
>> >'
>> > Range("A1:A20").Select
>> > Selection.Copy
>> > Range("C1").Select
>> > Range("C1").Select
>> > Selection.PasteSpecial Paste:=xlPasteAll,
>> Operation:=xlNone,
>> >SkipBlanks:= _
>> > False, Transpose:=True
>> > Range("A1:A20").Select
>> > Application.CutCopyMode = False
>> > Selection.Delete Shift:=xlUp
>> > Range("C2").Select
>> >End Sub
>> >
>> >--
>> >Thanks in advance for your help,
>> >Cathy S.
>> >.
>> >
>>
>.
>