Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MAKING A CONTRACT FROM DATA FROM A WORKSHEET | Excel Worksheet Functions | |||
VB copde help - please! | Excel Discussion (Misc queries) | |||
Validating data pasted into worksheet | Excel Discussion (Misc queries) | |||
Change position of move or copy worksheet option in Excel | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |