Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Annabelle
 
Posts: n/a
Default Move select data to another worksheet

I have a master worksheet that contains project report information. Of
the 300+ rows of data, I need to pull only the rows based on the
"Business Owner" column into another worksheet (or workbook) so I can
send only the project information specific to that business owner. The
manual alternative is to save the workbook with another name, delete
the rows that don't pertain to that business owner, save, and send. Is
there a formula or macro I can use to automate this process? I have
25-30 different business owners and the manual process is too time
consuming.

  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Annabelle,

Select a single cell within your database, and when prompted, input the number of the column within
that database that has the Business Owner data.

The macro will create separate files for each key value in that column.

HTH,
Bernie
MS Excel MVP




Sub ExportDatabaseToSeparateFiles()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name = myShtName Then
Exit Sub
Else
mySht.Move
ActiveWorkbook.SaveAs "Workbook " & ActiveSheet.Name & ".xls"
ActiveWorkbook.Close
End If
Next mySht
End Sub


"Annabelle" wrote in message
ps.com...
I have a master worksheet that contains project report information. Of
the 300+ rows of data, I need to pull only the rows based on the
"Business Owner" column into another worksheet (or workbook) so I can
send only the project information specific to that business owner. The
manual alternative is to save the workbook with another name, delete
the rows that don't pertain to that business owner, save, and send. Is
there a formula or macro I can use to automate this process? I have
25-30 different business owners and the manual process is too time
consuming.



  #3   Report Post  
FSt1
 
Posts: n/a
Default

hi,
Here is a "save range" macro i wrote sometimes back. you may wish to put it
in your personal file(i did) so that it will be available with any xl file. I
also put a custom icon up and assigned this macro to it.

Sub mac1SaveRange()

'Macro written by FSt1 4/27/03

Dim cnt As Long
Dim cell As Range

MsgBox "You have selected range" & Selection.Address
If Selection.Cells.Count = 1 Then
If MsgBox("You have selected only one cell. Continue?????", vbYesNo,
"Warning") = vbNo Then
Exit Sub
End If
End If
cnt = 0
For Each cell In Selection
If Not IsEmpty(cell) Then
cnt = cnt + 1
End If
Next
If cnt = 0 Then
If MsgBox("There is no data in the selected range. Continue?!?!?!?!?",
vbYesNo, "Warning") = vbNo Then
Exit Sub
End If
End If
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Application.Dialogs(xlDialogSaveAs).Show

End Sub

Regards

FSt1

"Annabelle" wrote:

I have a master worksheet that contains project report information. Of
the 300+ rows of data, I need to pull only the rows based on the
"Business Owner" column into another worksheet (or workbook) so I can
send only the project information specific to that business owner. The
manual alternative is to save the workbook with another name, delete
the rows that don't pertain to that business owner, save, and send. Is
there a formula or macro I can use to automate this process? I have
25-30 different business owners and the manual process is too time
consuming.


  #4   Report Post  
FSt1
 
Posts: n/a
Default

hi again.
I think i forgot to mention. High light the range you want saved to the new
WB.

FSt1

"Annabelle" wrote:

I have a master worksheet that contains project report information. Of
the 300+ rows of data, I need to pull only the rows based on the
"Business Owner" column into another worksheet (or workbook) so I can
send only the project information specific to that business owner. The
manual alternative is to save the workbook with another name, delete
the rows that don't pertain to that business owner, save, and send. Is
there a formula or macro I can use to automate this process? I have
25-30 different business owners and the manual process is too time
consuming.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
MAKING A CONTRACT FROM DATA FROM A WORKSHEET MCNAB Excel Worksheet Functions 5 July 28th 05 03:13 PM
VB copde help - please! Anthony Excel Discussion (Misc queries) 5 July 22nd 05 03:18 PM
Validating data pasted into worksheet Tom F. Excel Discussion (Misc queries) 3 July 20th 05 06:05 PM
Change position of move or copy worksheet option in Excel JesseAviles Excel Discussion (Misc queries) 1 February 22nd 05 11:25 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM


All times are GMT +1. The time now is 08:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"