formatting columns

D

Don

This is the first time I've posted to this group. I have some how managed
to get by using Excel while never having any training with it.
With that said here's my question/problem.
I also use Access and I'm exporting a query from Access to Excel. The query
has many dimensional entries. An example would be;
Part # 1
Hole Size 1 Hole Size 2 Hole Size 3
2.000 2.001 2.004
Part # 2
Hole Size 1 Hole Size 2 Hole Size 3
2.001 1.999 2.002
Part # 3
Hole Size 1 Hole Size 2 Hole Size 3
2.002 2.001 2.003
etc.
When this data is in Access and is exported into an Excel format it read
across, from left to right (landscape if you will).
This is where I'm looking for help. Is there a way to have Excel auto
format all of this data from left to right and make it top to bottom? Right
now I copy and paste (using paste special/transpose). As this amount of
information increases the task of formatting the columns as I'm doing now
will be a huge task.
Is there a way to do this that I'm missing?
If more information is needed just ask.
Thanks for your help,
Don....................
 
M

mrice

I'm not sure that I fully understand the question. Are you doing
separate copy-paste special for each part?

If so then a macro to reformat the data sound like a good way forward
Have a go at recording one for one of the parts and put it into
simple loop.

e.g.

Sub Process()
For N = 1 To 7 Step 3
Counter = Counter + 1
Cells(N + Counter, 5) = Cells(N, 1)
Cells(N + Counter + 1, 5) = Cells(N + 1, 1)
Cells(N + Counter + 2, 5) = Cells(N + 1, 2)
Cells(N + Counter + 3, 5) = Cells(N + 1, 3)
Cells(N + Counter + 1, 6) = Cells(N + 2, 1)
Cells(N + Counter + 2, 6) = Cells(N + 2, 2)
Cells(N + Counter + 3, 6) = Cells(N + 2, 3)
Next N
End Su
 
Top