Help with moving cells of info around.

[Paul]

I have created a worksheet that sorts data I copy from a
different file. The data after it is sorted is comprised
down to 3 columns of information with the first column
(AA) being the customer's name, followed by sales, ytd,
and last year total. I have a master list of all customers
in column b. What I want to do is sort the compressed data
based upon where the master record in located in the list
of rows.

Example

b10 = Smith Barney - aa3 = Smith Barney. I want to move
cell aa3-ad3 to cell aa10-ad10 to match up the records.
This could be done manually, but I have 6000 records to
adjust. Keep in mind that all information below aa3 needs
to move along with aa3-ad3 to keep all data in tack. To
add a little more complexity to this, the names are sorted
by Canadian and US customers. With empty cells separating
the two sets of information chains.

Can someone help me with a loop statement that will make
this easier than what I am doing now? (Manual)

Any help will be truly appreciated
Thanks in advance
Paul

 

[pikus]

Are the names ALWAYS listed exactly the same way? Always last nam
first and always all caps and always spelled the same way? - Piku

 

[pikus]

Try this:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim lrow As Long
Dim x As Long
Dim y As Long
Dim a As Long
Dim ws As Worksheet

Set ws = Worksheets(1)
lrow = ActiveSheet.UsedRange.Row - 1
ActiveSheet.UsedRange.Rows.Count

For x = 2 To lrow
cust = ws.Cells(x, 27).Value
For y = 2 To lrow
If ws.Cells(y, 1).Value = cust Then
ws.Range(ws.Cells(x, 27), ws.Cells(x, 30)).Cut
ws.Cells(y, 31).Select
ActiveSheet.Paste
End If
Next y
Next x
ws.Range(ws.Cells(1, 27), ws.Cells(1, 30)).Cut
ws.Cells(1, 31).Select
ActiveSheet.Paste
a = 1
For x = 2 To lrow
If ws.Cells(x, 27).Value <> "" Then
ws.Range(ws.Cells(x, 27), ws.Cells(x, 30)).Cut
ws.Cells(lrow + a, 31).Select
ActiveSheet.Paste
a = a + 1
End If
Next x
For x = 1 To 4
ws.Columns(27).Delete
Next x
Application.Goto ws.Cells(1, 1)
Application.ScreenUpdating = True
End Sub

- Piku

 

[Paul]

Pikus

This is Interesting that you put this code in a
CommandButton Format. I don't think I ever have used
something like this before? Can you help me, because when
I put this code into a CommandButton from the Control
ToolBox menu I can not figure out how to activate the
macro to begin to work?

Also if I just put the code into a normal sub file I get
all sorts of errors.

Any thoughts
Paul

 

[Paul]

Pikus

The compressed data in coloums aa-ad will be an ever
changing list of customers depending on who has boughten
from use in the past month. But the list in coloum b will
always remain the same unless we get a new customer that
has never boughten from us before. I just plan on adding
that customer name as it happens. The names are always
spelled the same and listed in the same manner (Alfa A-Z).

Paul

 

[pikus]

I don't believ any of the changes you mention should affect th
functioning of the ode. If you do want to put it into a button, inser
a button, enter design mode if you are not already there an
right-click the button and select "View Code" from the pop-up menu.
That will take you right there. - Piku