macro - how to move to a specific cell and repeat
 

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

macro - how to move to a specific cell and repeat
 

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" wrote in message
...
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

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" wrote in message
...
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

macro - how to move to a specific cell and repeat
 

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" wrote in message
...
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" wrote in message
...
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

macro - how to move to a specific cell and repeat
 

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" wrote in message
...
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" wrote in message
...
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

macro - how to move to a specific cell and repeat
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

macro - how to move to a specific cell and repeat
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

macro - how to move to a specific cell and repeat
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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