This is probably a pretty common question. Sorry!

Common Question

Hi Tracy,

At least you are honest. Most people that post here asking about macros are really looking for someone to do the work for them.

You can start by getting one of those "Dummies" books. I found them excellent for quick and easy learning.

If this is a task that you are going to be doing repeatedly, you can just record it the first time and then every time after that, run the macro and you are done.

There is one brilliant VBA programmer that could write this quickly, but I'm not even going to mention his name. It is not right nor fair for me to offer someone else's services.

As a "quick & dirty": From the tools menu go to Macro and then Macros and then Record a New Macro. Then make all your data moves. When done, end the "recording" session.

Hi Tracy

Not such a common question really! This could be done though by use of Loop that is Stepped by 17. See Below:

Sub TransPoseEach17()
Dim i As Integer
Dim lStop As Long
'Wriiten by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''''
'Transpose every 17 rows into 17 columns
'starting from Column B
'''''''''''''''''''''''''''''''''''''''''''''
Application.ScreenUpdating = False

i = 1
lStop = Range("A65536").End(xlUp).Row

For i = 1 To lStop Step 17
Range("A" & i & ":" & "A" & i + 16).Copy
Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues, , , Transpose:=True
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

This is designed to be run from the Sheet containing your Imported data. It will place every 17 rows as a group into 17 columns starting from Column B.

Regarding the learning Of VBA for Excel, my Company OzGrid Business Applications specializes in this type or training. I can and do conduct remote training/tutoring via email. All our courses are taior-made to suit the individual and the pace at which they run can range from 3 weeks to whenever. This pace is always set by the student. Should you be interested feel free to contact me at any time for all details.

Dave

OzGrid Business Applications

Rings a bell !!

Tracy, I may be wrong (wouldn't be the first time) but your name rings a bell. I think my company tried to contact you several times via email about 3-4 months ago regarding VBA for Excel training. Each time though it came back after 5 days saying the message could get through. Let me now if I'm right ?

Dave

OzGrid Business Applications

Re: Common Question

Kevin,
Unfortuneatly, I have tried the recorded macro suggestion, but transposing 12,000 records in this manner would be very time consuming and wouldn't make a very good business solution to this problem.
Thank you for the book suggestion, i'll give it another shot.
Tracy

Hi Tracy, At least you are honest. Most people that post here asking about macros are really looking for someone to do the work for them. You can start by getting one of those "Dummies" books. I found them excellent for quick and easy learning. If this is a task that you are going to be doing repeatedly, you can just record it the first time and then every time after that, run the macro and you are done. There is one brilliant VBA programmer that could write this quickly, but I'm not even going to mention his name. It is not right nor fair for me to offer someone else's services. As a "quick & dirty": From the tools menu go to Macro and then Macros and then Record a New Macro. Then make all your data moves. When done, end the "recording" session.

Dave,
Can you tell me what each of the lines is doing?
I know that the " ' " means to ignore this line as it is only for comments.
If you do not have the time to explain, I will understand.
Regardless, I thank you for your help and I did correspond to you regarding your last post.
THANKS A BUNCH!!!
Tracy

Hi Tracy

Here is the exact same code with lots of Comments added. Hope it helps, if
not let me know.

Sub TransPoseEach17()
'Declare Variables
Dim i As Integer 'Number in the range of -32,768 to +32,767.
Dim lStop As Long 'Number in the range of -2,147,483,648 to +2,147,483,647

'Wriiten by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''''
'Transpose every 17 rows into 17 columns
'starting from Column B
'''''''''''''''''''''''''''''''''''''''''''''

'Stops the screen repainting itself (screen flickering)
Application.ScreenUpdating = False

'Set our Integer variable "i" to one as the default is zero
i = 1
'Set our Long variable "lStop" to the last row
'of data in Column A. This would be the same as
'selecting cell A65536 (last row in Excel) and
'pushing Ctrl+(Up Arrow)
lStop = Range("A65536").End(xlUp).Row

'Loop as many times as the number returned by "lStop"
For i = 1 To lStop Step 17 'Increment by 17 NOT 1
'First loop this would be Range("A1:A17").Copy
'Second would be Range("A18:A34").Copy
'Third would be Range("A51:A68").Copy
'And so on.....
Range("A" & i & ":" & "A" & i + 16).Copy
'PasteSpecial our copied range to the first blank cell
'in Column B. But Transpose it ie; across columns.
Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues, , ,
Transpose:=True
Next i 'Loop again. This time add 17 to "i"

'Clear the ClipBoard
Application.CutCopyMode = False
'We need to turn this back on via code.
Application.ScreenUpdating = True

End Sub

Dave

OzGrid Business Applications

Here is the exact same code with lots of Comments added. Hope it helps, if

Dave,
Thank you for taking the time to do that!!!!
Regards,
Tracy Janecek