create macro to move label type data to column data

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:%[email protected]...
> That's what the code offered does.
>
> --
> Regards,
> Tom Ogilvy
>
> "JonathonWood9" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>