Copying to columns

  • Thread starter Thread starter cwilkin1
  • Start date Start date
C

cwilkin1

Hi, I have a list of 500 items in column A. I want to copy the first 50
to column

B1:B50, the next 50 to C1:C50 etc. Can someone show me how to do this
with a

simple macro?

Chuck
 
Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow Step 50
Cells(i, "A").Resize(50).Copy Cells(1, i \ 50 + 2)
Next i

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob said:
Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow Step 50
Cells(i, "A").Resize(50).Copy Cells(1, i \ 50 + 2)
Next i

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
Thanks, everything worked fine, you really solved my problem I know
next to

nothing about macros or VB,but hope to learn something for future use.
I have a

few questions:

1. After getting your post, I started to record a macro, stoped after
one entry

and erased the code and replaced it with yours. I came up with errors
such as
"Compile error expected End Sub ". then I noticed that there was
another macro

there "test" which I assumed came from the Subtest() in your macro. I
ran the

test macro and everything worked great. What is the best way to copy
code into

new macro? I went to Record new macro but did not know what choices to
make.




2.I'm trying to understand the macro as well as use it.
About iLastRow = Cells(Rows.Count, "A").

Is Rows.Count a VB term or is the in Excel like Counta or Countif

I tried to look up resizing but could not
find anything in my excell book

Any help in explaning how the macro works would be appreciated. I'm in
my 70's

and this comes really hard. But I do want to learn more.

Thanks,

Chuck
 
Thanks, everything worked fine, you really solved my problem I know
next to

nothing about macros or VB,but hope to learn something for future use.
I have a

few questions:

1. After getting your post, I started to record a macro, stoped after
one entry

and erased the code and replaced it with yours. I came up with errors
such as
"Compile error expected End Sub ". then I noticed that there was
another macro

there "test" which I assumed came from the Subtest() in your macro. I
ran the

test macro and everything worked great. What is the best way to copy
code into

new macro? I went to Record new macro but did not know what choices to
make.
Click to expand...


Sounds like you messed up copying the code in. You can actually record an
empty macro, just stop the recorder immediately, before doing anything.

Personally, I go to the VBIDE (Alt-F11), and insert a module
(Insert>Module), and enter the code directly.

What does it look like now, after your updates?

2.I'm trying to understand the macro as well as use it.
About iLastRow = Cells(Rows.Count, "A").

Is Rows.Count a VB term or is the in Excel like Counta or Countif
Click to expand...


It is Excel VBA constant, effectively created by Excel. It holds the number
of rows in the sheet.

I tried to look up resizing but could not
find anything in my excell book
Click to expand...


Resize does exactl;y what it says on the can, it changes the size. The
syntax is

expression.Resize(RowSize, ColumnSize)


expression refers to the thing being resized, a range in reality, rows and
columns refers to the size that therange being resized will be resized.

VBA Help says

Resizes the specified range. Returns a Range object that represents the
resized range.

expression.Resize(RowSize, ColumnSize)

expression Required. An expression that returns a Range object to be
resized.

RowSize Optional Variant. The number of rows in the new range. If this
argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range. If
this argument is omitted, the number of columns in the range remains the
same.


Any help in explaning how the macro works would be appreciated. I'm in
my 70's
Click to expand...


It is very simple,.
It first determines where the last row is.
It then steps through the rows 50 rows at a time.
It copies the current row, resized by 50 rows, to the first row in the next
column, which is calculated using the current row number, diving by 50,
taking the integer part of that division (i.e. 1\50 = 0, 51\50 =1) and
adding 2.
 
Bob said:
Sounds like you messed up copying the code in. You can actually record an
empty macro, just stop the recorder immediately, before doing anything.

Personally, I go to the VBIDE (Alt-F11), and insert a module
(Insert>Module), and enter the code directly.

What does it look like now, after your updates?




It is Excel VBA constant, effectively created by Excel. It holds the number
of rows in the sheet.




Resize does exactl;y what it says on the can, it changes the size. The
syntax is

expression.Resize(RowSize, ColumnSize)


expression refers to the thing being resized, a range in reality, rows and
columns refers to the size that therange being resized will be resized.

VBA Help says

Resizes the specified range. Returns a Range object that represents the
resized range.

expression.Resize(RowSize, ColumnSize)

expression Required. An expression that returns a Range object to be
resized.

RowSize Optional Variant. The number of rows in the new range. If this
argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range. If
this argument is omitted, the number of columns in the range remains the
same.





It is very simple,.
It first determines where the last row is.
It then steps through the rows 50 rows at a time.
It copies the current row, resized by 50 rows, to the first row in the next
column, which is calculated using the current row number, diving by 50,
taking the integer part of that division (i.e. 1\50 = 0, 51\50 =1) and
adding 2.
Click to expand...

Bob,
Thank you very much for taking the time to explain.


Chuck
 
Back
Top