I have address label data starting at B10 and then every 10 cells so c10,
d10, e10 etc all the way to B6270 i want to move the items in B11, 12 13 14
15 and 16, to column c,d,e,f,g,h so that i can export to Word, access etc
using column headings of Company name, add1 add2 etc.
How do i create a macro that does this. My first attempt did move the first
block B2 through B16 but did not move to b10 to start the next batch, so when
repeating the macro it went back to B2 and moved the now empty b3-b6 cells to
their "new" columns.
Also how will i delete rows 11 to 19 21-29 etc once the data is moved.
regards Jon

You might want to reread you post and then repost with a clearer
description. You start out in your description moving across columns and
suddenly end up at B6270.

If I understand you correctly, your data is every 10 rows, but you say your
code processes B2 to B16.

anyway see if this works

Sub ReorganizeData()
for i = 10 to 6270 step 10
cells(i,2).Resize(10,1).copy
cells(i,2).PasteSpecial xlValues, transpose:=True
cells(i+1,2).Resize(9,1).ClearContents
Next
set rng = Range("B10:B7000").specialCells(xlBlanks)
rng.EntireRow.Delete
End Sub

Run this on a copy of your data to see if it does what you want.
--
Regards,
Tom Ogilvy

"JonathonWood9" wrote in message
...
I have address label data starting at B10 and then every 10 cells so c10,
d10, e10 etc all the way to B6270 i want to move the items in B11, 12 13
14
15 and 16, to column c,d,e,f,g,h so that i can export to Word, access etc
using column headings of Company name, add1 add2 etc.
How do i create a macro that does this. My first attempt did move the
first
block B2 through B16 but did not move to b10 to start the next batch, so
when
repeating the macro it went back to B2 and moved the now empty b3-b6 cells
to
their "new" columns.
Also how will i delete rows 11 to 19 21-29 etc once the data is moved.
regards Jon

As Tom says this is not clear so:

i see from your response that i have not made things clear.

all my data is in column B....starting at row 10 there may be either 4,5 or
6 cells of data, which is why i restart the next label at 10.20.30 etc.

All i want to do is get these blocks into "name ad1 ad2" fields in access or
data that i can mail merge in word and i think to do that cell b11 needs to
move to c10, b12 needs to move to d10 etc and the the whole process start
again for the block starting in B20

regards Jon


"Tom Ogilvy" wrote:

You might want to reread you post and then repost with a clearer
description. You start out in your description moving across columns and
suddenly end up at B6270.

If I understand you correctly, your data is every 10 rows, but you say your
code processes B2 to B16.

anyway see if this works

Sub ReorganizeData()
for i = 10 to 6270 step 10
cells(i,2).Resize(10,1).copy
cells(i,2).PasteSpecial xlValues, transpose:=True
cells(i+1,2).Resize(9,1).ClearContents
Next
set rng = Range("B10:B7000").specialCells(xlBlanks)
rng.EntireRow.Delete
End Sub

Run this on a copy of your data to see if it does what you want.
--
Regards,
Tom Ogilvy

"JonathonWood9" wrote in message
...
I have address label data starting at B10 and then every 10 cells so c10,
d10, e10 etc all the way to B6270 i want to move the items in B11, 12 13
14
15 and 16, to column c,d,e,f,g,h so that i can export to Word, access etc
using column headings of Company name, add1 add2 etc.
How do i create a macro that does this. My first attempt did move the
first
block B2 through B16 but did not move to b10 to start the next batch, so
when
repeating the macro it went back to B2 and moved the now empty b3-b6 cells
to
their "new" columns.
Also how will i delete rows 11 to 19 21-29 etc once the data is moved.
regards Jon

That's what the code offered does.

--
Regards,
Tom Ogilvy

"JonathonWood9" wrote in message
...
As Tom says this is not clear so:

i see from your response that i have not made things clear.

all my data is in column B....starting at row 10 there may be either 4,5
or
6 cells of data, which is why i restart the next label at 10.20.30 etc.

All i want to do is get these blocks into "name ad1 ad2" fields in access
or
data that i can mail merge in word and i think to do that cell b11 needs
to
move to c10, b12 needs to move to d10 etc and the the whole process start
again for the block starting in B20

regards Jon


"Tom Ogilvy" wrote:

You might want to reread you post and then repost with a clearer
description. You start out in your description moving across columns
and
suddenly end up at B6270.

If I understand you correctly, your data is every 10 rows, but you say
your
code processes B2 to B16.

anyway see if this works

Sub ReorganizeData()
for i = 10 to 6270 step 10
cells(i,2).Resize(10,1).copy
cells(i,2).PasteSpecial xlValues, transpose:=True
cells(i+1,2).Resize(9,1).ClearContents
Next
set rng = Range("B10:B7000").specialCells(xlBlanks)
rng.EntireRow.Delete
End Sub

Run this on a copy of your data to see if it does what you want.
--
Regards,
Tom Ogilvy

"JonathonWood9" wrote in
message
...
I have address label data starting at B10 and then every 10 cells so
c10,
d10, e10 etc all the way to B6270 i want to move the items in B11, 12
13
14
15 and 16, to column c,d,e,f,g,h so that i can export to Word, access
etc
using column headings of Company name, add1 add2 etc.
How do i create a macro that does this. My first attempt did move the
first
block B2 through B16 but did not move to b10 to start the next batch,
so
when
repeating the macro it went back to B2 and moved the now empty b3-b6
cells
to
their "new" columns.
Also how will i delete rows 11 to 19 21-29 etc once the data is moved.
regards Jon

Here is a revision:

Sub ReorganizeData()
For i = 10 To 6270 Step 10
Cells(i, 2).Resize(10, 1).Copy
Cells(i, 3).PasteSpecial xlValues, Transpose:=True
Cells(i + 1, 2).Resize(9, 1).ClearContents
Next
Set rng = Range("B10:B7000").SpecialCells(xlBlanks)
rng.EntireRow.Delete
Columns(2).Delete
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
That's what the code offered does.

--
Regards,
Tom Ogilvy

"JonathonWood9" wrote in message
...
As Tom says this is not clear so:

i see from your response that i have not made things clear.

all my data is in column B....starting at row 10 there may be either 4,5
or
6 cells of data, which is why i restart the next label at 10.20.30 etc.

All i want to do is get these blocks into "name ad1 ad2" fields in
access
or
data that i can mail merge in word and i think to do that cell b11 needs
to
move to c10, b12 needs to move to d10 etc and the the whole process
start
again for the block starting in B20

regards Jon


"Tom Ogilvy" wrote:

You might want to reread you post and then repost with a clearer
description. You start out in your description moving across columns
and
suddenly end up at B6270.

If I understand you correctly, your data is every 10 rows, but you say
your
code processes B2 to B16.

anyway see if this works

Sub ReorganizeData()
for i = 10 to 6270 step 10
cells(i,2).Resize(10,1).copy
cells(i,2).PasteSpecial xlValues, transpose:=True
cells(i+1,2).Resize(9,1).ClearContents
Next
set rng = Range("B10:B7000").specialCells(xlBlanks)
rng.EntireRow.Delete
End Sub

Run this on a copy of your data to see if it does what you want.
--
Regards,
Tom Ogilvy

"JonathonWood9" wrote in
message
...
I have address label data starting at B10 and then every 10 cells so
c10,
d10, e10 etc all the way to B6270 i want to move the items in B11,
12
13
14
15 and 16, to column c,d,e,f,g,h so that i can export to Word,
access
etc
using column headings of Company name, add1 add2 etc.
How do i create a macro that does this. My first attempt did move
the
first
block B2 through B16 but did not move to b10 to start the next
batch,
so
when
repeating the macro it went back to B2 and moved the now empty b3-b6
cells
to
their "new" columns.
Also how will i delete rows 11 to 19 21-29 etc once the data is
moved.
regards Jon