Paste Cells in Column A to Columns B-K

  • Thread starter Thread starter George Plakas
  • Start date Start date
G

George Plakas

Hi All,

I was wondering if someone can help with writing an Excel Macro or
some scripting.

What I am trying to achieve is the following.

In Column A (A1:A1000) there are values of L0xxxxxx xxxxxx=numbers
(i.e L0123456)

What I want to be able to do is move the values from column A to
Columns B-K (10 Columns) 10 values at a time, upto 100 lines.

A B C D E F G H I J
K
L00001 L00001 L00002 L00003 L00004 .5 .6 .7 .8 .9
L00010
L00002 L00011
L00003
L00004
..5
..6
..7
..8
..9
L00010
L00011

Any help would be appreciated.

Thanks
George
 
I do not follow your example.

What determines the values in cols B to K? In your example it
is not obvious what dictates the values. What you write implies
that the cols B to K should have the SAME value as col A but
your example does not support this.

When you say "10 at a time" do you mean from A1:A10 into
all the appropriate cols or do you meant A1 to B1:K1?

Chrissy.


George Plakas wrote
 
Sorry Chrissy,

What I mean is to move from:-

Cells A1 to B1
Cells A2 to C1
Cells A3 to D1
Cells A4 to E1
Cells A5 to F1
Cells A6 to G1
Cells A7 to H1
Cells A8 to I1
Cells A9 to J1
Cells A10 to K1
Cells A11 to B2
Cells A12 to C2
Cells A13 to D2
Cells A14 to E2
Cells A15 to F2
Cells A16 to G2
Cells A17 to H2
Cells A18 to I2
Cells A19 to J2
Cells A20 to K2
Cells A21 to B3
Cells A22 to C3

ans so on.

Hope this helps.

Thanks
George
 
Sub MoveData()
Dim RNG As Range
Dim iCol As Integer
Dim iCols As Integer
Dim iRows As Integer
Dim iCount As Integer

iCol = 0
iCols = 10 ' Number of Cols you want to use
iRows = 1000 ' Number of Rows of Data

Set RNG = Range("A1") ' The Top Left of the Data

For iCount = 0 To iRows / iCols
For iCol = 1 To iCols
RNG.Offset(iCount, iCol) = RNG.Offset(iCount * iCols + iCol - 1, 0)
Next iCol
Next iCount
End Sub


Chrissy.
 
Hi,
You don't need a macro for this...

Do the following

1. Select all the cells you want to change(Copy)
2. create a new sheet
3. go to the paste special screen
4. check the "Transpose"
5. Click OK

If you don't see what you expected. Please le me know


Annamala
 
Chrissy said:
Sub MoveData()
Dim RNG As Range
Dim iCol As Integer
Dim iCols As Integer
Dim iRows As Integer
Dim iCount As Integer

iCol = 0
iCols = 10 ' Number of Cols you want to use
iRows = 1000 ' Number of Rows of Data

Set RNG = Range("A1") ' The Top Left of the Data

For iCount = 0 To iRows / iCols
For iCol = 1 To iCols
RNG.Offset(iCount, iCol) = RNG.Offset(iCount * iCols + iCol - 1, 0)
Next iCol
Next iCount
End Sub


Chrissy.
Click to expand...

Hi,
You don't need a macro for this...

Do the following

1. Select all the cells you want to change(Copy)
2. create a new sheet
3. go to the paste special screen
4. check the "Transpose"
5. Click OK

If you don't see what you expected. Please le me know


Annamala
 
What you suggest will take the 1000 cells, from cells A1:A1000
and put them into one line. That will not work as there are only
256 columns. Even if it did work, it would not give the OP what
he wanted which is a 10x100 grid of numbers from his original
list of 1000 cells.

You are correct that you do not need a macro. The way you
suggest is not the solution thought. To do it without a macro
you would enter this formula into each of the cells in the
10x100 grid where the results are needed. This requires the
original data to be in cells A1:An and the results to be in
cells B1:K(n/10) but it can be modified for any number of columns
and any destination area

=INDIRECT("A"&(ROW()-1)*10+COLUMN()-1)

Chrissy.



Annamala wrote
 
Hi Chrissy,

That worked fine, Thank You for you help.

Just quickly, is there a way to delete the contents in Column A as the
values moves accross to columns B-K. I know that if I run the following
macro it will replace whatever is in Column A for me with the value I
enter through the dialog box.

What I was wondering is, that I will not always have 100 rows, so is
there a way you can determine how many rows have been populated once all
values move from Column A, so when I enter the box no, it only populates
the rows (In Column A) that have the L0*.* value in columns B-K.

Any help would be appreciated and once again Thank You.


Sub BoxNo()
Dim userInput As Variant
x = InputBox("Enter a Box No") ' Enter, e.g., 1205
Range("A1:A100").Formula = "=" & x & ""
End Sub
 
Are you trying to put all the values into a giant matrix (A1:K100) with no blank
rows so you could delete column A?

If yes, how about this routine:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim NumberOfRows As Long
Dim LastRow As Long
Dim iRow As Long

With Worksheets("sheet1")
FirstRow = 1
NumberOfRows = 10
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step NumberOfRows
.Cells(iRow, "A").Resize(NumberOfRows, 1).Copy
.Cells(iRow, "B").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, Transpose:=True
Next iRow
.Range("b:b").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Range("a1").EntireColumn.Delete
End With

End Sub


It just goes down column A in groups of 10 rows and copies|pastespecial with a
transpose.

I started with this:

$A$1
$A$2
$A$3
$A$4
$A$5
$A$6
$A$7
$A$8
$A$9
$A$10
$A$11
$A$12
$A$13
$A$14
$A$15
$A$16
....
$A$100

And ended with this:

$A$1 $A$2 $A$3 $A$4 $A$5 $A$6 $A$7 $A$8 $A$9 $A$10
$A$11 $A$12 $A$13 $A$14 $A$15 $A$16 $A$17 $A$18 $A$19 $A$20
$A$21 $A$22 $A$23 $A$24 $A$25 $A$26 $A$27 $A$28 $A$29 $A$30
$A$31 $A$32 $A$33 $A$34 $A$35 $A$36 $A$37 $A$38 $A$39 $A$40
$A$41 $A$42 $A$43 $A$44 $A$45 $A$46 $A$47 $A$48 $A$49 $A$50
$A$51 $A$52 $A$53 $A$54 $A$55 $A$56 $A$57 $A$58 $A$59 $A$60
$A$61 $A$62 $A$63 $A$64 $A$65 $A$66 $A$67 $A$68 $A$69 $A$70
$A$71 $A$72 $A$73 $A$74 $A$75 $A$76 $A$77 $A$78 $A$79 $A$80
$A$81 $A$82 $A$83 $A$84 $A$85 $A$86 $A$87 $A$88 $A$89 $A$90
$A$91 $A$92 $A$93 $A$94 $A$95 $A$96 $A$97 $A$98 $A$99 $A$100
 
There is ALWAYS a way to do what ever you can dream up
EXCPET maybe (and I say maybe) washing the dishes and
the windows with Excel macros. In fact, there are many ways
to do the same thing.

The question is not CAN it be done but HOW SHOULD it be
done and WHAT EXACTLY you want done.

I suspect that you have decided that the way you have to do this
is to have a macro which does all the work and then you have to
have another macro to delete the original data etc. Maybe if you
explain what data you have and what you want then we can come
up with a technically more appropriate than others.

As far as what I can work out you are asking in this post, you could
just add code to delete the column or to clear the contents. Another
option would be to put the results in cols A to J and then clear the
contents of the remaining cells in col A at the end of the block - that
is cells A101:A1000 in your original example.

What you are trying to do with your BoxNo macro I do not know.
You define userImput and never use it. You use x and do not define it.
You enter the data "=" & x into the range A1:A100 this will put

=1205 (if the user enters 1205 into the input box)

into that range. This macro would do EXACTLY the same thing

Sub BoxNo()
Range("A1:A100").Formula = "=" & InputBox("Enter a Box No")
End Sub

There does not seem to be a point in using a formula there either. This
would look the same to the user.

Sub BoxNo()
Range("A1:A100") = InputBox("Enter a Box No")
End Sub


and the cells would contain

1205 (if the user enters 1205 into the input box)

So, what are you trying to do.

Chrissy.
 
Hi Chrissy,

Can I send you the spreadsheet I have and the log file that gets used to
an email address and I will also give you a detailed explanation of what
I am trying to achieve.

It would probably make things much easier to understand.

Thanks
George
 
Yes you can. The e-mail addy I post from just needs the xxx. removed
and is valid.

Chrissy.

George Plakas wrote
 
Back
Top