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.

0
a_bolland (26)
7/27/2005 4:33:25 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
356 Views

Similar Articles

[PageSpeed] 0

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(before:=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" <a_bolland@yahoo.com> wrote in message 
news:1122482005.138928.8910@g44g2000cwa.googlegroups.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.
> 


0
Bernie
7/27/2005 4:54:23 PM
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.
> 
> 
0
FSt1 (238)
7/27/2005 5:00:02 PM
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.
> 
> 
0
FSt1 (238)
7/27/2005 5:01:04 PM
Reply:

Similar Artilces:

Allow comments to be added to protected worksheet?
I have created a worsksheet with selected areas locked and then protected it. My users want to be able to add comments where necessary but are not able to. I cant seem to find anything in the help menu - any ideas? When protecting the sheet "allow users to:" Select unlocked cells and Edit Objects. Note: with edit objects checked, users can insert comments in locked cells if they are allowed to select those. Gord Dibben MS Excel MVP On Wed, 4 Nov 2009 15:00:02 -0800, sue@solotel <suesolotel@discussions.microsoft.com> wrote: >I have created a worsksheet with sel...

copy cells from one worksheet to another
How do I make the "value" or content of a cell on 'worksheet 1' mirror or copy the font color (and strike through) of a cell on a seperate worksheet? - 'worksheet 2'? thanks. Copy and paste special|values followed by Paste special|formats If you were using a formula, then that formula can only return the value--it can't change the format. Depending on how that other cell changed, you could have an event macro do the work for you. Garrett wrote: > > How do I make the "value" or content of a cell on 'worksheet 1' mirror or > copy ...

Moving license to a new computer
I bought a powerbook and am giving the kids my old imac. I'd like to move Office to the powerbook and remove it from the imac. Am I going to run into any technical difficulties doing this? In article <do3en0hjuiv1jrgsan1tlf23demblohh9p@4ax.com>, Andrew D <andrew.davilman@mindspring.com> wrote: > I bought a powerbook and am giving the kids my old imac. I'd like to > move Office to the powerbook and remove it from the imac. Am I going > to run into any technical difficulties doing this? Shouldn't. Run the Remove Office application on the iMac, and inst...

not able to move FLAG STATUS column in Outlook 2003
I tried to move FLAG STATUS column from the very right side to the left side when I am in my Inbox. IT WON'T LET ME!! Anyone was able to do that? No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Kenny Shu asked: | I tried to move FLAG STATUS column from the very right | side to the left side when I am in my Inbox. IT WON'T LET | ME!! | | Anyone was abl...

Bring filtered data from another file
I have a Birthdays file I have a Calendar file After each date in this calendar file I have 4 empty lines (assumption: max 4 birthdays on the same day) 15.01.2010 ......... ......... ......... ......... 16.01.2010 ......... ......... ......... ......... How can I bring the names for each date from the Birthdays file. Thanks and regards, CousinExcel ...

How do I print grid lines in a spreadsheet if no data is entered .
I am trying to create a form and need to print the grid lines as a part of the form. Others will use this form and make pencil entries in the cells which will be entered into the spreadsheet at a later date. How do I do this? I am using Office 2003. Many thanks---Justaone Just File>Page Setup>Sheet. Check "gridlines" and make sure "draft quality" is not checked. May be easier and more readable to have borders around just those cells that will be pencil-entered. If you go that route, uncheck "gridlines" in Page Setup. Gord Dibben Excel MVP On Sun, 2...

Safe to Move?
Hello, I remember seeing an article about moving $NtServicePackUninstall$ files to free up space. I think it was for Windows 2000 Server. Is it possible to do this for Windows 2003 server? -- Thank you for your help! JYC Hello Mr. JYC, Yes, you can do it and if are sure that there is no need to uninstall it you can delete it. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ** Please do NOT email, only reply to Newsgroups ** HELP us help YOU!!! http://www.blakjak.demon.co.uk/mul_crss.ht...

Creating a new worksheet from a template
I would like to press a button on one worksheet and create a ne worksheet that conforms to a template. However: 1) this is for a series of people who do not work centrally and who ar not technical i.e. the solution needs to be self-contained within m workbook and not involve the creation of xlst templates that have t sit in a certain place on the hard drive. 2) These worksheets will have a series of fields that relate t 'activities'. The user can chose to spawn worksheets for one or mor activities. I would like a summary sheet to add all the activit information from as many activit...

Copy or Move row on condition to different and/or multiple sheets
Good afternoon. I'm new to the world of programming in excel but have programmed elsewhere previously. What i am trying to do is to have a todo list setup, ive searched long and hard and can't find anything that quite fits my bill that i can replicate and alter to work so am asking for some assistance. I have a workbook with six sheets, All Jobs, Evaluation, Authority, In Progress, Completed and a helper sheet. In all jobs i have 9 columns A-I (atm A being spare) that have information that i need to show. In column 'I' i have a dropdown box using the helper sheet for p...

Live Mail,I can't move message "unknown Error" appears
-- There are several messages that I want to place in folders, but when I try to move them, I receive the message "Unable to move, Unknown Error". This is more than annoying, as these are important messages from various organizations I belong to. I like to archive these within the confines of Live Mail, and I don't want to hve to go in and out to various folders. Any help will be appreciated. Windows Live Mail Newsgroup. In your newsreader: news://msnews.microsoft.com/microsoft.public.windows.live.mail.desktop On the Web: http://www.microsoft.com/communiti...

Conv Access 97->2000-Gets a method or data member not found error
For the following code which compiles and runs successfully in MS Access 97, when converting to MS Access 2000 the following compile error occurs "Method or data member not found." for the line below. tempID = rs.[UserID] UserID is a field in the tblSecurity table. It seems that the tblSecurity table is not opening in MS Access 2000 since this error will occur using any field, not just UserID. This error does not occur in MS Access 97. PLEASE HELP. The routine is displayed below. Sincerely thanks, Phil ------------------ CODE - Shown Below ------------------ Private Sub...

Moving Exchange 2000
Hi, I have a exchange 2000 server running on windows 2000 which I need to move onto a new server/hardware but cannot find techincal documents telling me how to do this. The only documents I can find are on disaster recovery which doesnt seem to cover moving exchange across onto new hardware. Does anybody have some a document ??? Also I would like to run exchange 2000 on server 2003, I'm guessing that this won't be a problem to move it onto new hardware running server 2003 ?? Kind Regards Paul... Use the move-server method detailed here: http://tinyurl.com/ys7e9 -- Neil H...

Read Receipts won't move to 'delete bin' after processing
Very simply, my read receipts won't move to 'delete bin' after they are processed. This doesn't seem like a complex process. The tracking option allows me to either turn this rule on or off. What else am I supposed to do? I have receipts all over the place and I end up deleteting them manually. Hy, You have to delete it by yourself. It wont work with a rule. >-----Original Message----- >Very simply, my read receipts won't move to 'delete bin' after they are processed. This doesn't seem like a complex process. The tracking option allows me to either ...

Go to section of all worksheets
I have set the following macro to unhide column Y in all sheets. Dim sh As Worksheet For Each sh In Sheets(Array("sheet1", "sheet2", "sheet3")) sh.Columns("X:Z").EntireColumn.Hidden = False Range("H7").Select Next However, how do I also get it to select sell H7 and also scroll to the top left of the workbook in all sheets? One way: Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In Sheets(Array("sheet1", "sheet2", "sheet3")) With ...

mail moved from netscape to outlook?
Hello! I would like to know is it possible to move the mails from netscape mail to outlook? And what is the way? Thanks! >-----Original Message----- >Hello! I would like to know is it possible to move the >mails from netscape mail to outlook? And what is the way? >Thanks! >. > <anonymous@discussions.microsoft.com> wrote in message news:d08901c3efb2$759a75e0$a101280a@phx.gbl... > > >-----Original Message----- > >Hello! I would like to know is it possible to move the > >mails from netscape mail to outlook? And what is the way? > >Thanks! ...

Unable to select multiple workbook sheets
I am using Excel 2k running under WinXP and have a problem I have never encountered on any other version or machine. All my workbook tabs are white instead of the default gray and only one sheet at a time can be selected. Neither the Ctrl or shift keys or right clicking the tabs and selecting all will allow multiple sheets to be selected. What am I missing here, I see nothing in options that allows this configuration? Detect and repair does not change the situation. If this is a new situation, could you have a Mouse Driver compatability issue? I've heard of some interestin...

Another question concerning powershell and exchange 2003
i am really strugling with this process. I see examples on the net for doing this but they all do a lot more than I want so there is a ton of code to sift through. Basicly I will query AD for a user account. I want to be able to create the mailbox for that account. Do you have a simple example of what i need to do to get access to CDOEXM and what parameters it is wanting. I assume that is where my trouble is. ...

Moving Pages between two publications
How can I move several entire pages from one document to another? Not simply the content of the page, but the format as well as positioning? Select all the elements on one of the pages and Group them together (a little icon will appear at the bottom of the bounding box - click it to lock the group) before you Copy and paste to the other publication. "FrankDe" <FrankDe@discussions.microsoft.com> wrote in message news:B2DF180E-4973-43D3-8278-7EDDF897EA60@microsoft.com... | How can I move several entire pages from one document to another? Not simply | the content of the ...

Move mailbox rights in Exchange 5.5 #2
What is the most restrictive set of rights that would allow an administrator to move mailboxes between 2 servers in the same site? Providing the administrator an admin role on the Servers container, on each server, or on the Private/Public Information Store gives him undesired privileges (like changing default storage limits). Thanks in advance for any assistance. ...

moving companies moving loans moving budget
moving companies moving loans moving budget http://www.movingcompanies.co.il ...

data in queries
Hi! From a dataset I retrieve customer names and the products they have purchased: client name product name client 1 product a client 1 product b client 1 product c client 2 product b client 2 product d client 3 product a client 3 product e No problem. But how can I avoid the repetitions in the ClientName column: client name product name client 1 product a product b product c client 2 product b product d client 3 product a product e In Query View. I think in a Report this is done through Group By? Thanks ...

Move users from none Business Unit to other
hi, is it possible to move a user from one business unit to other ? Yes it is. However when you move them they will lose their security role so you will need to add a security role to their user record "M. Ceccacci" <anonymous@discussions.microsoft.com> wrote in message news:D9C93DCE-6400-4C1F-BE36-C86832BA9DCD@microsoft.com... > hi, is it possible to move a user from one business unit to other ? should have said!! to do this go to settings, business units, users then open up the users record then click actions - change business unit. once done make sure you reassign...

Does Excel data merge with .mrg documents?
I run a database for my company, and always have a difficult (read - lots of hand typing) time getting my .mrg data doc to merge with Excel. Most times Exel will have all the data in one cell (making it impossible to sort), or not come through at all. Is it just me, or does Excel not work well with this kind of document. ...

Moving from Outlook on PC to Entourage on Mac
I'm trying to move to a Mac from a laptop that died and use the Office 2008 for Mac. Can't find the Pst file on the laptop hard drive and it doesn't seem easy to import from outlook. Any suggestions? Locations are here http://www.howto-outlook.com/howto/backupandrestore.htm Outlook requires the data file to be located on your current system, (Not so it overwrites any existing data file) before you open it within Outlook (or import) "GeorgeK" <GeorgeK@discussions.microsoft.com> wrote in message news:64D74ACC-C994-441B-8EA1-FDC4D30B3F88@microsoft.co...

Select with partial group
Hi I have a clients table on which I want to run a select query on to extract all fields. But I also want the result-set to be grouped by Company and Postcode to remove any duplicates. The reason I don't want to include other fields Address line 1 etc as there could be minor defences in spellings, punctuations etc. How do I go about such a query? Thanks Regards iam fine "John" <John@nospam.infovis.co.uk> wrote in message news:upGpvwqDIHA.3548@TK2MSFTNGP06.phx.gbl... > Hi > > I have a clients table on which I want to run a select query on to extract >...