Macro - Help Please Excel 2003

Hi

I am hoping that somebody may be able to help with this

I am trying to semi automate a worksheet to cut and paste Company account
information within the same spreadsheet

For example one of the worksheets may have 60 rows I need to select Company
A - cut it and paste it into a blank row + 2 then go on to to select Company
B and perform the same operation. The entire spreadsheet has about 50 or so
worksheets - getting this sorted out would save me a lot of time

Any help would be much appreciated


Thank you

A little more detail about what you want to do might be helpful. For your
existing data... can a company take up more than one row? If so, would those
rows be consecutive or could they be scattered all about? If scattered all
about, did you want them all placed together, one after the other? Where on
the same sheet did you want the companies moved to... in rows after the
currently existing last row? If so, is that where you want them to remain?
Or would you want them moved up? Also, exactly what did you mean by "cut it
and paste it into a blank row + 2"? Did you want the current information
that is currently on a single row split up onto 3 rows somehow? If so, how
did you want them split? Or did you mean you wanted the single row to remain
a single row, but to have two blank rows separating it from the next
company's data?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Hi

I am hoping that somebody may be able to help with this

I am trying to semi automate a worksheet to cut and paste Company account
information within the same spreadsheet

For example one of the worksheets may have 60 rows I need to select
Company
A - cut it and paste it into a blank row + 2 then go on to to select
Company
B and perform the same operation. The entire spreadsheet has about 50 or
so
worksheets - getting this sorted out would save me a lot of time

Any help would be much appreciated


Thank you

Rick - thank you for the quick response and apolog
ies that my initial information was misleading

Spreadsheet with various worksheets represnting different countries - the
data is not consistent in so much as one worksheet may contain 200 rows and
another only 60.

There are common Companies in each worksheet : ie UPS World Wide 1 - UPS
World Wide 2 - TNT Global 1 - TNT Global 2 and so on.
On each of these sheets I would like to iremove these companies from the
rest and cut and paste them into blank rows commencing at 2 rows below the
final entry which would be moveable at each operation - the total rows for
these Companies and their subs could range from 50 entries to 3 entries

Attempting to use a macro for this using auto filter and copying and then
deleting the visible rows causes errors as the data ranges are not consistent
and you end up with overlapping rows so you cannot delete.

What I was looking for is for something like an if function to identify the
next blank row + 2 and then paste the data and then delete those duplicates
from the original data - the next stage would then be the manual process to
create a total beneath each group

I hope you can help

Thanks

Paul


"Rick Rothstein" wrote:

A little more detail about what you want to do might be helpful. For your
existing data... can a company take up more than one row? If so, would those
rows be consecutive or could they be scattered all about? If scattered all
about, did you want them all placed together, one after the other? Where on
the same sheet did you want the companies moved to... in rows after the
currently existing last row? If so, is that where you want them to remain?
Or would you want them moved up? Also, exactly what did you mean by "cut it
and paste it into a blank row + 2"? Did you want the current information
that is currently on a single row split up onto 3 rows somehow? If so, how
did you want them split? Or did you mean you wanted the single row to remain
a single row, but to have two blank rows separating it from the next
company's data?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Hi

I am hoping that somebody may be able to help with this

I am trying to semi automate a worksheet to cut and paste Company account
information within the same spreadsheet

For example one of the worksheets may have 60 rows I need to select
Company
A - cut it and paste it into a blank row + 2 then go on to to select
Company
B and perform the same operation. The entire spreadsheet has about 50 or
so
worksheets - getting this sorted out would save me a lot of time

Any help would be much appreciated


Thank you

Okay, I'm still not completely sure of what you want (but I think I'm
closeg). As I understand it, you have "common companies" which can appear
on multiple worksheets and it is the row for these companies that you want
moved; you want each worksheet treated independently from the rest (that is,
only the data for the common companies on a sheet should be moved on *that*
sheet); and the data being moved on each sheet should start on the third row
beneath the last row of data on that sheet and the row of data that are move
will appear one under the next with *no* intervening empty rows between
them. Is that correct? If so, is there a list of these "common companies"
somewhere (listed in a column on one of your sheets somewhere that can be
referenced in code or, perhaps, just a listing that can be hard-coded in the
macro directly)?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Rick - thank you for the quick response and apolog
ies that my initial information was misleading

Spreadsheet with various worksheets represnting different countries - the
data is not consistent in so much as one worksheet may contain 200 rows
and
another only 60.

There are common Companies in each worksheet : ie UPS World Wide 1 - UPS
World Wide 2 - TNT Global 1 - TNT Global 2 and so on.
On each of these sheets I would like to iremove these companies from the
rest and cut and paste them into blank rows commencing at 2 rows below the
final entry which would be moveable at each operation - the total rows
for
these Companies and their subs could range from 50 entries to 3 entries

Attempting to use a macro for this using auto filter and copying and then
deleting the visible rows causes errors as the data ranges are not
consistent
and you end up with overlapping rows so you cannot delete.

What I was looking for is for something like an if function to identify
the
next blank row + 2 and then paste the data and then delete those
duplicates
from the original data - the next stage would then be the manual process
to
create a total beneath each group

I hope you can help

Thanks

Paul


"Rick Rothstein" wrote:

A little more detail about what you want to do might be helpful. For your
existing data... can a company take up more than one row? If so, would
those
rows be consecutive or could they be scattered all about? If scattered
all
about, did you want them all placed together, one after the other? Where
on
the same sheet did you want the companies moved to... in rows after the
currently existing last row? If so, is that where you want them to
remain?
Or would you want them moved up? Also, exactly what did you mean by "cut
it
and paste it into a blank row + 2"? Did you want the current information
that is currently on a single row split up onto 3 rows somehow? If so,
how
did you want them split? Or did you mean you wanted the single row to
remain
a single row, but to have two blank rows separating it from the next
company's data?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Hi

I am hoping that somebody may be able to help with this

I am trying to semi automate a worksheet to cut and paste Company
account
information within the same spreadsheet

For example one of the worksheets may have 60 rows I need to select
Company
A - cut it and paste it into a blank row + 2 then go on to to select
Company
B and perform the same operation. The entire spreadsheet has about 50
or
so
worksheets - getting this sorted out would save me a lot of time

Any help would be much appreciated


Thank you

Thanks Rick - you made more sense - the situation is how you summarized

Common company names contain the text TNT - UPS - Fedex :

Grateful for you taking this amount of time

Paul

"Rick Rothstein" wrote:

Okay, I'm still not completely sure of what you want (but I think I'm
closeg). As I understand it, you have "common companies" which can appear
on multiple worksheets and it is the row for these companies that you want
moved; you want each worksheet treated independently from the rest (that is,
only the data for the common companies on a sheet should be moved on *that*
sheet); and the data being moved on each sheet should start on the third row
beneath the last row of data on that sheet and the row of data that are move
will appear one under the next with *no* intervening empty rows between
them. Is that correct? If so, is there a list of these "common companies"
somewhere (listed in a column on one of your sheets somewhere that can be
referenced in code or, perhaps, just a listing that can be hard-coded in the
macro directly)?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Rick - thank you for the quick response and apolog
ies that my initial information was misleading

Spreadsheet with various worksheets represnting different countries - the
data is not consistent in so much as one worksheet may contain 200 rows
and
another only 60.

There are common Companies in each worksheet : ie UPS World Wide 1 - UPS
World Wide 2 - TNT Global 1 - TNT Global 2 and so on.
On each of these sheets I would like to iremove these companies from the
rest and cut and paste them into blank rows commencing at 2 rows below the
final entry which would be moveable at each operation - the total rows
for
these Companies and their subs could range from 50 entries to 3 entries

Attempting to use a macro for this using auto filter and copying and then
deleting the visible rows causes errors as the data ranges are not
consistent
and you end up with overlapping rows so you cannot delete.

What I was looking for is for something like an if function to identify
the
next blank row + 2 and then paste the data and then delete those
duplicates
from the original data - the next stage would then be the manual process
to
create a total beneath each group

I hope you can help

Thanks

Paul


"Rick Rothstein" wrote:

A little more detail about what you want to do might be helpful. For your
existing data... can a company take up more than one row? If so, would
those
rows be consecutive or could they be scattered all about? If scattered
all
about, did you want them all placed together, one after the other? Where
on
the same sheet did you want the companies moved to... in rows after the
currently existing last row? If so, is that where you want them to
remain?
Or would you want them moved up? Also, exactly what did you mean by "cut
it
and paste it into a blank row + 2"? Did you want the current information
that is currently on a single row split up onto 3 rows somehow? If so,
how
did you want them split? Or did you mean you wanted the single row to
remain
a single row, but to have two blank rows separating it from the next
company's data?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Hi

I am hoping that somebody may be able to help with this

I am trying to semi automate a worksheet to cut and paste Company
account
information within the same spreadsheet

For example one of the worksheets may have 60 rows I need to select
Company
A - cut it and paste it into a blank row + 2 then go on to to select
Company
B and perform the same operation. The entire spreadsheet has about 50
or
so
worksheets - getting this sorted out would save me a lot of time

Any help would be much appreciated


Thank you

As with all macros that change data, you should test the macro out on a
copied workbook to make sure it works the way you want and expect. The
reason? Changes to worksheets made by macros cannot be undone. Also, it is
important to note that if the names of any of your companies contain the
letters of what you called the "common companies", then they will also be
moved. For example, if you had a company named Federated Insurance Groups Of
America, this company name would be picked up as a UPS company because those
letters appear in the word "Groups" in its name. I might be able to trap for
this kind of misidentification, but it depends on where in the name the
"common companies" name part appears. For example, are these "common
company" name parts always separated from the rest of the name by a space?
If so, do they appear at the beginning, middle or end of the full company
name?

With these warnings in mind, give this macro a try (note that I assumed your
company names are in Column A; if this is wrong, change the column letter in
the Const statement)...

Sub MoveCommonCompaniesToTheEnd()
Dim X As Long
Dim MovedDataStartRow As Long
Dim R As Range
Dim ToMove As Range
Dim WS As Worksheet
Dim FirstAddress As String
Dim CompanyNames() As String
' Column to search for common company names
Const CompanyNamesRow As String = "A"
' Comma delimited list; no spaces around commas
Const Companies As String = "TNT,UPS,Fedex"
CompanyNames = Split(Companies, ",")
For Each WS In Worksheets
MovedDataStartRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row + 3
For X = 0 To UBound(CompanyNames)
Set ToMove = Nothing
Set R = WS.Columns(CompanyNamesRow).Find(What:=CompanyName s(X), _
After:=WS.Cells(WS.Rows.Count, CompanyNamesRow), _
LookAt:=xlPart, MatchCase:=False)
If Not R Is Nothing Then
FirstAddress = R.Address
Do
If ToMove Is Nothing Then
Set ToMove = R.EntireRow
Else
Set ToMove = Union(R.EntireRow, ToMove)
End If
Set R = WS.Columns(CompanyNamesRow).FindNext(R)
Loop While Not R Is Nothing And R.Address FirstAddress
ToMove.Copy WS.Cells(MovedDataStartRow, "A")
ToMove.Delete
MovedDataStartRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row + 1
End If
Next
Next
End Sub

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Thanks Rick - you made more sense - the situation is how you summarized

Common company names contain the text TNT - UPS - Fedex :

Grateful for you taking this amount of time

Paul

"Rick Rothstein" wrote:

Okay, I'm still not completely sure of what you want (but I think I'm
closeg). As I understand it, you have "common companies" which can
appear
on multiple worksheets and it is the row for these companies that you
want
moved; you want each worksheet treated independently from the rest (that
is,
only the data for the common companies on a sheet should be moved on
*that*
sheet); and the data being moved on each sheet should start on the third
row
beneath the last row of data on that sheet and the row of data that are
move
will appear one under the next with *no* intervening empty rows between
them. Is that correct? If so, is there a list of these "common companies"
somewhere (listed in a column on one of your sheets somewhere that can be
referenced in code or, perhaps, just a listing that can be hard-coded in
the
macro directly)?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Rick - thank you for the quick response and apolog
ies that my initial information was misleading

Spreadsheet with various worksheets represnting different countries -
the
data is not consistent in so much as one worksheet may contain 200 rows
and
another only 60.

There are common Companies in each worksheet : ie UPS World Wide 1 -
UPS
World Wide 2 - TNT Global 1 - TNT Global 2 and so on.
On each of these sheets I would like to iremove these companies from
the
rest and cut and paste them into blank rows commencing at 2 rows below
the
final entry which would be moveable at each operation - the total rows
for
these Companies and their subs could range from 50 entries to 3 entries

Attempting to use a macro for this using auto filter and copying and
then
deleting the visible rows causes errors as the data ranges are not
consistent
and you end up with overlapping rows so you cannot delete.

What I was looking for is for something like an if function to identify
the
next blank row + 2 and then paste the data and then delete those
duplicates
from the original data - the next stage would then be the manual
process
to
create a total beneath each group

I hope you can help

Thanks

Paul


"Rick Rothstein" wrote:

A little more detail about what you want to do might be helpful. For
your
existing data... can a company take up more than one row? If so, would
those
rows be consecutive or could they be scattered all about? If scattered
all
about, did you want them all placed together, one after the other?
Where
on
the same sheet did you want the companies moved to... in rows after
the
currently existing last row? If so, is that where you want them to
remain?
Or would you want them moved up? Also, exactly what did you mean by
"cut
it
and paste it into a blank row + 2"? Did you want the current
information
that is currently on a single row split up onto 3 rows somehow? If so,
how
did you want them split? Or did you mean you wanted the single row to
remain
a single row, but to have two blank rows separating it from the next
company's data?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Hi

I am hoping that somebody may be able to help with this

I am trying to semi automate a worksheet to cut and paste Company
account
information within the same spreadsheet

For example one of the worksheets may have 60 rows I need to select
Company
A - cut it and paste it into a blank row + 2 then go on to to select
Company
B and perform the same operation. The entire spreadsheet has about
50
or
so
worksheets - getting this sorted out would save me a lot of time

Any help would be much appreciated


Thank you

Rick

This works a treat you are brilliant !

You were right about the common name, UPS v Groups but it is only 1 Company
and 2 lines...I have tried to amend the construct by using External Sales
Groups
with no joy - it may easier to get the Country to redefine the name

Thank you very much for all your help - it is very much appreciated

Paul



"Rick Rothstein" wrote:

As with all macros that change data, you should test the macro out on a
copied workbook to make sure it works the way you want and expect. The
reason? Changes to worksheets made by macros cannot be undone. Also, it is
important to note that if the names of any of your companies contain the
letters of what you called the "common companies", then they will also be
moved. For example, if you had a company named Federated Insurance Groups Of
America, this company name would be picked up as a UPS company because those
letters appear in the word "Groups" in its name. I might be able to trap for
this kind of misidentification, but it depends on where in the name the
"common companies" name part appears. For example, are these "common
company" name parts always separated from the rest of the name by a space?
If so, do they appear at the beginning, middle or end of the full company
name?

With these warnings in mind, give this macro a try (note that I assumed your
company names are in Column A; if this is wrong, change the column letter in
the Const statement)...

Sub MoveCommonCompaniesToTheEnd()
Dim X As Long
Dim MovedDataStartRow As Long
Dim R As Range
Dim ToMove As Range
Dim WS As Worksheet
Dim FirstAddress As String
Dim CompanyNames() As String
' Column to search for common company names
Const CompanyNamesRow As String = "A"
' Comma delimited list; no spaces around commas
Const Companies As String = "TNT,UPS,Fedex"
CompanyNames = Split(Companies, ",")
For Each WS In Worksheets
MovedDataStartRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row + 3
For X = 0 To UBound(CompanyNames)
Set ToMove = Nothing
Set R = WS.Columns(CompanyNamesRow).Find(What:=CompanyName s(X), _
After:=WS.Cells(WS.Rows.Count, CompanyNamesRow), _
LookAt:=xlPart, MatchCase:=False)
If Not R Is Nothing Then
FirstAddress = R.Address
Do
If ToMove Is Nothing Then
Set ToMove = R.EntireRow
Else
Set ToMove = Union(R.EntireRow, ToMove)
End If
Set R = WS.Columns(CompanyNamesRow).FindNext(R)
Loop While Not R Is Nothing And R.Address FirstAddress
ToMove.Copy WS.Cells(MovedDataStartRow, "A")
ToMove.Delete
MovedDataStartRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row + 1
End If
Next
Next
End Sub

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Thanks Rick - you made more sense - the situation is how you summarized

Common company names contain the text TNT - UPS - Fedex :

Grateful for you taking this amount of time

Paul

"Rick Rothstein" wrote:

Okay, I'm still not completely sure of what you want (but I think I'm
closeg). As I understand it, you have "common companies" which can
appear
on multiple worksheets and it is the row for these companies that you
want
moved; you want each worksheet treated independently from the rest (that
is,
only the data for the common companies on a sheet should be moved on
*that*
sheet); and the data being moved on each sheet should start on the third
row
beneath the last row of data on that sheet and the row of data that are
move
will appear one under the next with *no* intervening empty rows between
them. Is that correct? If so, is there a list of these "common companies"
somewhere (listed in a column on one of your sheets somewhere that can be
referenced in code or, perhaps, just a listing that can be hard-coded in
the
macro directly)?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Rick - thank you for the quick response and apolog
ies that my initial information was misleading

Spreadsheet with various worksheets represnting different countries -
the
data is not consistent in so much as one worksheet may contain 200 rows
and
another only 60.

There are common Companies in each worksheet : ie UPS World Wide 1 -
UPS
World Wide 2 - TNT Global 1 - TNT Global 2 and so on.
On each of these sheets I would like to iremove these companies from
the
rest and cut and paste them into blank rows commencing at 2 rows below
the
final entry which would be moveable at each operation - the total rows
for
these Companies and their subs could range from 50 entries to 3 entries

Attempting to use a macro for this using auto filter and copying and
then
deleting the visible rows causes errors as the data ranges are not
consistent
and you end up with overlapping rows so you cannot delete.

What I was looking for is for something like an if function to identify
the
next blank row + 2 and then paste the data and then delete those
duplicates
from the original data - the next stage would then be the manual
process
to
create a total beneath each group

I hope you can help

Thanks

Paul


"Rick Rothstein" wrote:

A little more detail about what you want to do might be helpful. For
your
existing data... can a company take up more than one row? If so, would
those
rows be consecutive or could they be scattered all about? If scattered
all
about, did you want them all placed together, one after the other?
Where
on
the same sheet did you want the companies moved to... in rows after
the
currently existing last row? If so, is that where you want them to
remain?
Or would you want them moved up? Also, exactly what did you mean by
"cut
it
and paste it into a blank row + 2"? Did you want the current
information
that is currently on a single row split up onto 3 rows somehow? If so,
how
did you want them split? Or did you mean you wanted the single row to
remain
a single row, but to have two blank rows separating it from the next
company's data?

--
Rick (MVP - Excel)


"Paul" Not_ wrote in message
...
Hi

I am hoping that somebody may be able to help with this

I am trying to semi automate a worksheet to cut and paste Company
account
information within the same spreadsheet

For example one of the worksheets may have 60 rows I need to select
Company
A - cut it and paste it into a blank row + 2 then go on to to select
Company
B and perform the same operation. The entire spreadsheet has about
50
or
so
worksheets - getting this sorted out would save me a lot of time

Any help would be much appreciated


Thank you