macro - how to move to a specific cell and repeat

[andrea]

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 Moehrbach]

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]

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]

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]

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]

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]

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]

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