macro - how to move to a specific cell and repeat

Hi Otto
Thank you for your interest in my problem.
The spreadsheet is currently...
Columns A-F make up a unique account no - op unit, site, account etc,
Columns G-R are the months
Columns A-F Columns G-R
Account no A Jan, Feb.......Dec
Account no B Jan, Feb.......Dec

Require...
Row 1 Account no A Jan
Row 2 Account no A Feb
Row 12 Account no A Dec

Row 13 Account no B Jan
Row 14 Account no B Feb
Row 26 Account no B Dec
etc

So, far my basic macro is as follows:
Sub copy_transpose()
'
' copy_transpose Macro
' Macro recorded 11/08/2008 by
'

'
Range("A3:F3").Select
Selection.copy
Range("A20:A31").Select
ActiveSheet.Paste
Range("G3:R3").Select
Application.CutCopyMode = False
Selection.copy
Range("G20").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Range("A4").Select
End Sub

What I need it to do is return to cell A4, repeat the process and continue
for the remaining data.
(BTW the data will be pasted into a different worksheet, have remained in
the existing sheet at the moment will I played!)

Hope this makes sense
Andrea


"Otto Moehrbach" wrote:

> Andrea
> You will have to provide a bit more information regarding the layout of
> your data. For instance, what data are you copying/transposing (what rows,
> what columns, etc.). Also, what constitutes "the next row"? A good idea
> would be for you write down the steps of what you do when you do this
> manually, step-by-step, leaving nothing out. HTH Otto
> "andrea" <andrea.RemoveThis@discussions.microsoft.com> wrote in message
> news:D1F39477-928B-485F-9518-274DE056EF85@microsoft.com...
> >I want to create a macro that copies and transposes data, returns to the
> >next
> > row of data and repeat the process until the end. I'm ok with the copy
> > and
> > transpose bit, just the last bit.
> >
> > Thanks
>
>

Andrea
I modified your macro by taking out the extraneous stuff and added the
code to loop through all the rows. I used a second sheet as the destination
sheet and named it "Destination". Data starts in the Destination sheet in
row 2. I assumed the source sheet was named "Source". Change these names
in the code (one place for each name) to fit what you have. Come back if
this doesn't do what you want. Be sure to try this out on a copy of your
real file first. HTH Otto
Sub copy_transpose()
Dim rColA As Range
Dim i As Range
Dim Dest As Range
Dim DestRow As Long
Sheets("Source").Select
'The source sheet is the active sheet
Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
With Sheets("Destination") 'The destination sheet
Set Dest = .Range("A2")
For Each i In rColA
i.Resize(, 6).Copy 'Columns A:F
Dest.PasteSpecial
i.Offset(, 6).Resize(, 12).Copy 'Columns G:R
Dest.Offset(, 6).PasteSpecial Transpose:=True
DestRow = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
Set Dest = .Cells(DestRow, 1)
Next i
End With
Application.ScreenUpdating = True
End Sub

"andrea" <andrea.DeleteThis@discussions.microsoft.com> wrote in message
news:B1D35061-CC0B-4875-9F0C-86EA651D23D6@microsoft.com...
> Hi Otto
> Thank you for your interest in my problem.
> The spreadsheet is currently...
> Columns A-F make up a unique account no - op unit, site, account etc,
> Columns G-R are the months
> Columns A-F Columns G-R
> Account no A Jan, Feb.......Dec
> Account no B Jan, Feb.......Dec
>
> Require...
> Row 1 Account no A Jan
> Row 2 Account no A Feb
> Row 12 Account no A Dec
>
> Row 13 Account no B Jan
> Row 14 Account no B Feb
> Row 26 Account no B Dec
> etc
>
> So, far my basic macro is as follows:
> Sub copy_transpose()
> '
> ' copy_transpose Macro
> ' Macro recorded 11/08/2008 by
> '
>
> '
> Range("A3:F3").Select
> Selection.copy
> Range("A20:A31").Select
> ActiveSheet.Paste
> Range("G3:R3").Select
> Application.CutCopyMode = False
> Selection.copy
> Range("G20").Select
> Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=True
> Range("A4").Select
> End Sub
>
> What I need it to do is return to cell A4, repeat the process and continue
> for the remaining data.
> (BTW the data will be pasted into a different worksheet, have remained in
> the existing sheet at the moment will I played!)
>
> Hope this makes sense
> Andrea
>
>
> "Otto Moehrbach" wrote:
>
>> Andrea
>> You will have to provide a bit more information regarding the layout
>> of
>> your data. For instance, what data are you copying/transposing (what
>> rows,
>> what columns, etc.). Also, what constitutes "the next row"? A good idea
>> would be for you write down the steps of what you do when you do this
>> manually, step-by-step, leaving nothing out. HTH Otto
>> "andrea" <andrea.DeleteThis@discussions.microsoft.com> wrote in message
>> news:D1F39477-928B-485F-9518-274DE056EF85@microsoft.com...
>> >I want to create a macro that copies and transposes data, returns to the
>> >next
>> > row of data and repeat the process until the end. I'm ok with the copy
>> > and
>> > transpose bit, just the last bit.
>> >
>> > Thanks
>>
>>

Thanks Otto - almost there!
For columns A:F I need this to be copied down (repeated) 12 times to match
the transposed data. At the moment it appears once every 12 rows.
Cheers
Andrea



"Otto Moehrbach" wrote:

> Andrea
> I modified your macro by taking out the extraneous stuff and added the
> code to loop through all the rows. I used a second sheet as the destination
> sheet and named it "Destination". Data starts in the Destination sheet in
> row 2. I assumed the source sheet was named "Source". Change these names
> in the code (one place for each name) to fit what you have. Come back if
> this doesn't do what you want. Be sure to try this out on a copy of your
> real file first. HTH Otto
> Sub copy_transpose()
> Dim rColA As Range
> Dim i As Range
> Dim Dest As Range
> Dim DestRow As Long
> Sheets("Source").Select
> 'The source sheet is the active sheet
> Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
> Application.ScreenUpdating = False
> With Sheets("Destination") 'The destination sheet
> Set Dest = .Range("A2")
> For Each i In rColA
> i.Resize(, 6).Copy 'Columns A:F
> Dest.PasteSpecial
> i.Offset(, 6).Resize(, 12).Copy 'Columns G:R
> Dest.Offset(, 6).PasteSpecial Transpose:=True
> DestRow = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
> Set Dest = .Cells(DestRow, 1)
> Next i
> End With
> Application.ScreenUpdating = True
> End Sub
>
> "andrea" <andrea.RemoveThis@discussions.microsoft.com> wrote in message
> news:B1D35061-CC0B-4875-9F0C-86EA651D23D6@microsoft.com...
> > Hi Otto
> > Thank you for your interest in my problem.
> > The spreadsheet is currently...
> > Columns A-F make up a unique account no - op unit, site, account etc,
> > Columns G-R are the months
> > Columns A-F Columns G-R
> > Account no A Jan, Feb.......Dec
> > Account no B Jan, Feb.......Dec
> >
> > Require...
> > Row 1 Account no A Jan
> > Row 2 Account no A Feb
> > Row 12 Account no A Dec
> >
> > Row 13 Account no B Jan
> > Row 14 Account no B Feb
> > Row 26 Account no B Dec
> > etc
> >
> > So, far my basic macro is as follows:
> > Sub copy_transpose()
> > '
> > ' copy_transpose Macro
> > ' Macro recorded 11/08/2008 by
> > '
> >
> > '
> > Range("A3:F3").Select
> > Selection.copy
> > Range("A20:A31").Select
> > ActiveSheet.Paste
> > Range("G3:R3").Select
> > Application.CutCopyMode = False
> > Selection.copy
> > Range("G20").Select
> > Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> > SkipBlanks:= _
> > False, Transpose:=True
> > Range("A4").Select
> > End Sub
> >
> > What I need it to do is return to cell A4, repeat the process and continue
> > for the remaining data.
> > (BTW the data will be pasted into a different worksheet, have remained in
> > the existing sheet at the moment will I played!)
> >
> > Hope this makes sense
> > Andrea
> >
> >
> > "Otto Moehrbach" wrote:
> >
> >> Andrea
> >> You will have to provide a bit more information regarding the layout
> >> of
> >> your data. For instance, what data are you copying/transposing (what
> >> rows,
> >> what columns, etc.). Also, what constitutes "the next row"? A good idea
> >> would be for you write down the steps of what you do when you do this
> >> manually, step-by-step, leaving nothing out. HTH Otto
> >> "andrea" <andrea.RemoveThis@discussions.microsoft.com> wrote in message
> >> news:D1F39477-928B-485F-9518-274DE056EF85@microsoft.com...
> >> >I want to create a macro that copies and transposes data, returns to the
> >> >next
> >> > row of data and repeat the process until the end. I'm ok with the copy
> >> > and
> >> > transpose bit, just the last bit.
> >> >
> >> > Thanks
> >>
> >>
>
>

Andrea
Try this. Otto
Sub copy_transpose()
Dim rColA As Range
Dim i As Range
Dim Dest As Range
Sheets("Source").Select
'The source sheet is the active sheet
Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
With Sheets("Destination") 'The destination sheet
Set Dest = .Range("A2")
For Each i In rColA
i.Resize(, 6).Copy 'Columns A:F
Dest.Resize(12).PasteSpecial
i.Offset(, 6).Resize(, 12).Copy 'Columns G:R
Dest.Offset(, 6).PasteSpecial Transpose:=True
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
"andrea" <andrea.RemoveThis@discussions.microsoft.com> wrote in message
news:275EADBA-E53B-433C-9D93-09F13CFABCB5@microsoft.com...
> Thanks Otto - almost there!
> For columns A:F I need this to be copied down (repeated) 12 times to match
> the transposed data. At the moment it appears once every 12 rows.
> Cheers
> Andrea
>
>
>
> "Otto Moehrbach" wrote:
>
>> Andrea
>> I modified your macro by taking out the extraneous stuff and added
>> the
>> code to loop through all the rows. I used a second sheet as the
>> destination
>> sheet and named it "Destination". Data starts in the Destination sheet
>> in
>> row 2. I assumed the source sheet was named "Source". Change these
>> names
>> in the code (one place for each name) to fit what you have. Come back if
>> this doesn't do what you want. Be sure to try this out on a copy of your
>> real file first. HTH Otto
>> Sub copy_transpose()
>> Dim rColA As Range
>> Dim i As Range
>> Dim Dest As Range
>> Dim DestRow As Long
>> Sheets("Source").Select
>> 'The source sheet is the active sheet
>> Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
>> Application.ScreenUpdating = False
>> With Sheets("Destination") 'The destination sheet
>> Set Dest = .Range("A2")
>> For Each i In rColA
>> i.Resize(, 6).Copy 'Columns A:F
>> Dest.PasteSpecial
>> i.Offset(, 6).Resize(, 12).Copy 'Columns G:R
>> Dest.Offset(, 6).PasteSpecial Transpose:=True
>> DestRow = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
>> Set Dest = .Cells(DestRow, 1)
>> Next i
>> End With
>> Application.ScreenUpdating = True
>> End Sub
>>
>> "andrea" <andrea.RemoveThis@discussions.microsoft.com> wrote in message
>> news:B1D35061-CC0B-4875-9F0C-86EA651D23D6@microsoft.com...
>> > Hi Otto
>> > Thank you for your interest in my problem.
>> > The spreadsheet is currently...
>> > Columns A-F make up a unique account no - op unit, site, account etc,
>> > Columns G-R are the months
>> > Columns A-F Columns G-R
>> > Account no A Jan, Feb.......Dec
>> > Account no B Jan, Feb.......Dec
>> >
>> > Require...
>> > Row 1 Account no A Jan
>> > Row 2 Account no A Feb
>> > Row 12 Account no A Dec
>> >
>> > Row 13 Account no B Jan
>> > Row 14 Account no B Feb
>> > Row 26 Account no B Dec
>> > etc
>> >
>> > So, far my basic macro is as follows:
>> > Sub copy_transpose()
>> > '
>> > ' copy_transpose Macro
>> > ' Macro recorded 11/08/2008 by
>> > '
>> >
>> > '
>> > Range("A3:F3").Select
>> > Selection.copy
>> > Range("A20:A31").Select
>> > ActiveSheet.Paste
>> > Range("G3:R3").Select
>> > Application.CutCopyMode = False
>> > Selection.copy
>> > Range("G20").Select
>> > Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
>> > SkipBlanks:= _
>> > False, Transpose:=True
>> > Range("A4").Select
>> > End Sub
>> >
>> > What I need it to do is return to cell A4, repeat the process and
>> > continue
>> > for the remaining data.
>> > (BTW the data will be pasted into a different worksheet, have remained
>> > in
>> > the existing sheet at the moment will I played!)
>> >
>> > Hope this makes sense
>> > Andrea
>> >
>> >
>> > "Otto Moehrbach" wrote:
>> >
>> >> Andrea
>> >> You will have to provide a bit more information regarding the
>> >> layout
>> >> of
>> >> your data. For instance, what data are you copying/transposing (what
>> >> rows,
>> >> what columns, etc.). Also, what constitutes "the next row"? A good
>> >> idea
>> >> would be for you write down the steps of what you do when you do this
>> >> manually, step-by-step, leaving nothing out. HTH Otto
>> >> "andrea" <andrea.RemoveThis@discussions.microsoft.com> wrote in message
>> >> news:D1F39477-928B-485F-9518-274DE056EF85@microsoft.com...
>> >> >I want to create a macro that copies and transposes data, returns to
>> >> >the
>> >> >next
>> >> > row of data and repeat the process until the end. I'm ok with the
>> >> > copy
>> >> > and
>> >> > transpose bit, just the last bit.
>> >> >
>> >> > Thanks
>> >>
>> >>
>>
>>

Otto
Thanks for this - yes it worked a treat!

Any suggestions to how/where I can learn more about this?

Appreciate your time.
Cheers
Andrea

"Otto Moehrbach" wrote:

> Andrea
> Try this. Otto
> Sub copy_transpose()
> Dim rColA As Range
> Dim i As Range
> Dim Dest As Range
> Sheets("Source").Select
> 'The source sheet is the active sheet
> Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
> Application.ScreenUpdating = False
> With Sheets("Destination") 'The destination sheet
> Set Dest = .Range("A2")
> For Each i In rColA
> i.Resize(, 6).Copy 'Columns A:F
> Dest.Resize(12).PasteSpecial
> i.Offset(, 6).Resize(, 12).Copy 'Columns G:R
> Dest.Offset(, 6).PasteSpecial Transpose:=True
> Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
> Next i
> End With
> Application.CutCopyMode = False
> Application.ScreenUpdating = True
> End Sub
> "andrea" <andrea.RemoveThis@discussions.microsoft.com> wrote in message
> news:275EADBA-E53B-433C-9D93-09F13CFABCB5@microsoft.com...
> > Thanks Otto - almost there!
> > For columns A:F I need this to be copied down (repeated) 12 times to match
> > the transposed data. At the moment it appears once every 12 rows.
> > Cheers
> > Andrea
> >
> >
> >
> > "Otto Moehrbach" wrote:
> >
> >> Andrea
> >> I modified your macro by taking out the extraneous stuff and added
> >> the
> >> code to loop through all the rows. I used a second sheet as the
> >> destination
> >> sheet and named it "Destination". Data starts in the Destination sheet
> >> in
> >> row 2. I assumed the source sheet was named "Source". Change these
> >> names
> >> in the code (one place for each name) to fit what you have. Come back if
> >> this doesn't do what you want. Be sure to try this out on a copy of your
> >> real file first. HTH Otto
> >> Sub copy_transpose()
> >> Dim rColA As Range
> >> Dim i As Range
> >> Dim Dest As Range
> >> Dim DestRow As Long
> >> Sheets("Source").Select
> >> 'The source sheet is the active sheet
> >> Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
> >> Application.ScreenUpdating = False
> >> With Sheets("Destination") 'The destination sheet
> >> Set Dest = .Range("A2")
> >> For Each i In rColA
> >> i.Resize(, 6).Copy 'Columns A:F
> >> Dest.PasteSpecial
> >> i.Offset(, 6).Resize(, 12).Copy 'Columns G:R
> >> Dest.Offset(, 6).PasteSpecial Transpose:=True
> >> DestRow = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
> >> Set Dest = .Cells(DestRow, 1)
> >> Next i
> >> End With
> >> Application.ScreenUpdating = True
> >> End Sub
> >>
> >> "andrea" <andrea.RemoveThis@discussions.microsoft.com> wrote in message
> >> news:B1D35061-CC0B-4875-9F0C-86EA651D23D6@microsoft.com...
> >> > Hi Otto
> >> > Thank you for your interest in my problem.
> >> > The spreadsheet is currently...
> >> > Columns A-F make up a unique account no - op unit, site, account etc,
> >> > Columns G-R are the months
> >> > Columns A-F Columns G-R
> >> > Account no A Jan, Feb.......Dec
> >> > Account no B Jan, Feb.......Dec
> >> >
> >> > Require...
> >> > Row 1 Account no A Jan
> >> > Row 2 Account no A Feb
> >> > Row 12 Account no A Dec
> >> >
> >> > Row 13 Account no B Jan
> >> > Row 14 Account no B Feb
> >> > Row 26 Account no B Dec
> >> > etc
> >> >
> >> > So, far my basic macro is as follows:
> >> > Sub copy_transpose()
> >> > '
> >> > ' copy_transpose Macro
> >> > ' Macro recorded 11/08/2008 by
> >> > '
> >> >
> >> > '
> >> > Range("A3:F3").Select
> >> > Selection.copy
> >> > Range("A20:A31").Select
> >> > ActiveSheet.Paste
> >> > Range("G3:R3").Select
> >> > Application.CutCopyMode = False
> >> > Selection.copy
> >> > Range("G20").Select
> >> > Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> >> > SkipBlanks:= _
> >> > False, Transpose:=True
> >> > Range("A4").Select
> >> > End Sub
> >> >
> >> > What I need it to do is return to cell A4, repeat the process and
> >> > continue
> >> > for the remaining data.
> >> > (BTW the data will be pasted into a different worksheet, have remained
> >> > in
> >> > the existing sheet at the moment will I played!)
> >> >
> >> > Hope this makes sense
> >> > Andrea
> >> >
> >> >
> >> > "Otto Moehrbach" wrote:
> >> >
> >> >> Andrea
> >> >> You will have to provide a bit more information regarding the
> >> >> layout
> >> >> of
> >> >> your data. For instance, what data are you copying/transposing (what
> >> >> rows,
> >> >> what columns, etc.). Also, what constitutes "the next row"? A good
> >> >> idea
> >> >> would be for you write down the steps of what you do when you do this
> >> >> manually, step-by-step, leaving nothing out. HTH Otto
> >> >> "andrea" <andrea.RemoveThis@discussions.microsoft.com> wrote in message
> >> >> news:D1F39477-928B-485F-9518-274DE056EF85@microsoft.com...
> >> >> >I want to create a macro that copies and transposes data, returns to
> >> >> >the
> >> >> >next
> >> >> > row of data and repeat the process until the end. I'm ok with the
> >> >> > copy
> >> >> > and
> >> >> > transpose bit, just the last bit.
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >>
> >>
>
>

Andrea
You can peruse these Excel newsgroups and look at the questions and
responses. Also you can purchase a book written by John Walkenbach named
Microsoft Office Excel XXXX Power Programming With VBA. The "XXXX" is the
latest version of Excel that the particular book covers. The book covers
all the versions before that also. The "XXXX" can be 2000, 2002, 2003, or
2007. I recommend that you purchase the latest book regardless of what
version of Excel you have. I have found Amazon.com to give the best price.
HTH Otto
"andrea" <andrea.DeleteThis@discussions.microsoft.com> wrote in message
news:A11C8FF7-6B7A-4B13-A00A-28942EA364EA@microsoft.com...
> Otto
> Thanks for this - yes it worked a treat!
>
> Any suggestions to how/where I can learn more about this?
>
> Appreciate your time.
> Cheers
> Andrea
>
> "Otto Moehrbach" wrote:
>
>> Andrea
>> Try this. Otto
>> Sub copy_transpose()
>> Dim rColA As Range
>> Dim i As Range
>> Dim Dest As Range
>> Sheets("Source").Select
>> 'The source sheet is the active sheet
>> Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
>> Application.ScreenUpdating = False
>> With Sheets("Destination") 'The destination sheet
>> Set Dest = .Range("A2")
>> For Each i In rColA
>> i.Resize(, 6).Copy 'Columns A:F
>> Dest.Resize(12).PasteSpecial
>> i.Offset(, 6).Resize(, 12).Copy 'Columns G:R
>> Dest.Offset(, 6).PasteSpecial Transpose:=True
>> Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
>> Next i
>> End With
>> Application.CutCopyMode = False
>> Application.ScreenUpdating = True
>> End Sub
>> "andrea" <andrea.DeleteThis@discussions.microsoft.com> wrote in message
>> news:275EADBA-E53B-433C-9D93-09F13CFABCB5@microsoft.com...
>> > Thanks Otto - almost there!
>> > For columns A:F I need this to be copied down (repeated) 12 times to
>> > match
>> > the transposed data. At the moment it appears once every 12 rows.
>> > Cheers
>> > Andrea
>> >
>> >
>> >
>> > "Otto Moehrbach" wrote:
>> >
>> >> Andrea
>> >> I modified your macro by taking out the extraneous stuff and added
>> >> the
>> >> code to loop through all the rows. I used a second sheet as the
>> >> destination
>> >> sheet and named it "Destination". Data starts in the Destination
>> >> sheet
>> >> in
>> >> row 2. I assumed the source sheet was named "Source". Change these
>> >> names
>> >> in the code (one place for each name) to fit what you have. Come back
>> >> if
>> >> this doesn't do what you want. Be sure to try this out on a copy of
>> >> your
>> >> real file first. HTH Otto
>> >> Sub copy_transpose()
>> >> Dim rColA As Range
>> >> Dim i As Range
>> >> Dim Dest As Range
>> >> Dim DestRow As Long
>> >> Sheets("Source").Select
>> >> 'The source sheet is the active sheet
>> >> Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
>> >> Application.ScreenUpdating = False
>> >> With Sheets("Destination") 'The destination sheet
>> >> Set Dest = .Range("A2")
>> >> For Each i In rColA
>> >> i.Resize(, 6).Copy 'Columns A:F
>> >> Dest.PasteSpecial
>> >> i.Offset(, 6).Resize(, 12).Copy 'Columns G:R
>> >> Dest.Offset(, 6).PasteSpecial Transpose:=True
>> >> DestRow = .Range("G" & Rows.Count).End(xlUp).Offset(1).Row
>> >> Set Dest = .Cells(DestRow, 1)
>> >> Next i
>> >> End With
>> >> Application.ScreenUpdating = True
>> >> End Sub
>> >>
>> >> "andrea" <andrea.DeleteThis@discussions.microsoft.com> wrote in message
>> >> news:B1D35061-CC0B-4875-9F0C-86EA651D23D6@microsoft.com...
>> >> > Hi Otto
>> >> > Thank you for your interest in my problem.
>> >> > The spreadsheet is currently...
>> >> > Columns A-F make up a unique account no - op unit, site, account
>> >> > etc,
>> >> > Columns G-R are the months
>> >> > Columns A-F Columns G-R
>> >> > Account no A Jan, Feb.......Dec
>> >> > Account no B Jan, Feb.......Dec
>> >> >
>> >> > Require...
>> >> > Row 1 Account no A Jan
>> >> > Row 2 Account no A Feb
>> >> > Row 12 Account no A Dec
>> >> >
>> >> > Row 13 Account no B Jan
>> >> > Row 14 Account no B Feb
>> >> > Row 26 Account no B Dec
>> >> > etc
>> >> >
>> >> > So, far my basic macro is as follows:
>> >> > Sub copy_transpose()
>> >> > '
>> >> > ' copy_transpose Macro
>> >> > ' Macro recorded 11/08/2008 by
>> >> > '
>> >> >
>> >> > '
>> >> > Range("A3:F3").Select
>> >> > Selection.copy
>> >> > Range("A20:A31").Select
>> >> > ActiveSheet.Paste
>> >> > Range("G3:R3").Select
>> >> > Application.CutCopyMode = False
>> >> > Selection.copy
>> >> > Range("G20").Select
>> >> > Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
>> >> > SkipBlanks:= _
>> >> > False, Transpose:=True
>> >> > Range("A4").Select
>> >> > End Sub
>> >> >
>> >> > What I need it to do is return to cell A4, repeat the process and
>> >> > continue
>> >> > for the remaining data.
>> >> > (BTW the data will be pasted into a different worksheet, have
>> >> > remained
>> >> > in
>> >> > the existing sheet at the moment will I played!)
>> >> >
>> >> > Hope this makes sense
>> >> > Andrea
>> >> >
>> >> >
>> >> > "Otto Moehrbach" wrote:
>> >> >
>> >> >> Andrea
>> >> >> You will have to provide a bit more information regarding the
>> >> >> layout
>> >> >> of
>> >> >> your data. For instance, what data are you copying/transposing
>> >> >> (what
>> >> >> rows,
>> >> >> what columns, etc.). Also, what constitutes "the next row"? A
>> >> >> good
>> >> >> idea
>> >> >> would be for you write down the steps of what you do when you do
>> >> >> this
>> >> >> manually, step-by-step, leaving nothing out. HTH Otto
>> >> >> "andrea" <andrea.DeleteThis@discussions.microsoft.com> wrote in message
>> >> >> news:D1F39477-928B-485F-9518-274DE056EF85@microsoft.com...
>> >> >> >I want to create a macro that copies and transposes data, returns
>> >> >> >to
>> >> >> >the
>> >> >> >next
>> >> >> > row of data and repeat the process until the end. I'm ok with
>> >> >> > the
>> >> >> > copy
>> >> >> > and
>> >> >> > transpose bit, just the last bit.
>> >> >> >
>> >> >> > Thanks
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>