Macro Help

M

Mdub

Hi,

I have a problem creating a macro to cut and paste data from on
spreadsheet to another.

I have a large spreadsheet with a few thousand entries, which detail
names and departments. One column has the name of the persons manger i
it.

What I would like to do is write a macro to sort the contents of th
spreadsheet by manager, select the first group of entries (all thos
reporting to the manager) cut them from the spreadsheet and paste the
into a new one call call the new spread sheet "manager".xls

Id like the macro to do this until no data is left in the origina
spreadsheet.

I've killed by brain trying to work this out, but to no avail, ha
anyone done a similar thing.

All help is gratefully accepted

Marty
 
D

Don Guillett

Have you tried recording a macro while you do it manually? Then you can get
the idea and modify to use something like:

x=sheets("yoursourcesheet").cells(rows.count,"a").end(xlup).row+1
sheets("sourcesheet").range("a1:x21").copy
sheets("destinationsheet").range("a"&x)

to determine the next available row to paste to
 
B

Bernie Deitrick

Martyn,

The macro below will break a database out into different sheets, based on
the values in the selected column. As written, the key column needs to be
the first column of your database.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String

For Each myCell In Selection
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter field:=1, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
.AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub
 
D

Dave Peterson

You have one more reply at your other thread.

Mdub < said:
Hi,

I have a problem creating a macro to cut and paste data from one
spreadsheet to another.

I have a large spreadsheet with a few thousand entries, which details
names and departments. One column has the name of the persons manger in
it.

What I would like to do is write a macro to sort the contents of the
spreadsheet by manager, select the first group of entries (all those
reporting to the manager) cut them from the spreadsheet and paste them
into a new one call call the new spread sheet "manager".xls

Id like the macro to do this until no data is left in the original
spreadsheet.

I've killed by brain trying to work this out, but to no avail, has
anyone done a similar thing.

All help is gratefully accepted

Martyn
Click to expand...
 
Top