Export siingle rows into new workbook

Hi there

I have an excel file full of data (about 1000 rows).  I need to export
each row into a new workbook (just one row in each workbook, resulting
in about 1000 files), and then save it so that the filename is the
same as the data from one of the columns.  E.g. cell C2 = 'A001-07'
which is what the file should be saved as, and C3 is 'A001-08' which
is what that row's file should be saved as....and so on....

I normally use VBA in access not excel and while I know what I want
todo, I am having a bit of trouble!  I guess I will have to put some
kind of a loop in for each row?

Any help and suggestions would be very much appreciated.

Thanks in advance

Andy
0
6/29/2009 7:19:26 PM
excel 39879 articles. 2 followers. Follow

2 Replies
328 Views

Similar Articles

[PageSpeed] 32

This may get you started:

Option Explicit
Sub testme03()
    Dim wks As Worksheet
    Dim NewWks As Worksheet
    Dim myRng As Range
    Dim myCell As Range
    
    Set wks = Worksheets("Sheet1")
    Set NewWks = Worksheets.Add
    
    With wks
        Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
    End With
    
    For Each myCell In myRng.Cells
        myCell.EntireRow.Copy _
            Destination:=NewWks.Range("A1")
        NewWks.Parent.SaveAs _
            Filename:="C:\somefolder\" & myCell.Value & ".xls", _
            FileFormat:=xlWorkbookNormal
    Next myCell
    
    NewWks.Parent.Close savechanges:=False
    
End Sub




Hypnotic_Monkey_Scratcher wrote:
> 
> Hi there
> 
> I have an excel file full of data (about 1000 rows).  I need to export
> each row into a new workbook (just one row in each workbook, resulting
> in about 1000 files), and then save it so that the filename is the
> same as the data from one of the columns.  E.g. cell C2 = 'A001-07'
> which is what the file should be saved as, and C3 is 'A001-08' which
> is what that row's file should be saved as....and so on....
> 
> I normally use VBA in access not excel and while I know what I want
> todo, I am having a bit of trouble!  I guess I will have to put some
> kind of a loop in for each row?
> 
> Any help and suggestions would be very much appreciated.
> 
> Thanks in advance
> 
> Andy

-- 

Dave Peterson
0
petersod (12005)
7/10/2009 7:27:29 PM
Don't use my first suggestion.  There's a bug in it.

Option Explicit
Sub testme03()
    Dim wks As Worksheet
    Dim NewWks As Worksheet
    Dim myRng As Range
    Dim myCell As Range
    
    Set wks = Worksheets("Sheet1")
    Set NewWks = Workbooks.Add(1).Worksheets(1)
    
    With wks
        Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
    End With
    
    For Each myCell In myRng.Cells
        myCell.EntireRow.Copy _
            Destination:=NewWks.Range("A1")
        NewWks.Parent.SaveAs _
            Filename:="C:\somefolder" & myCell.Value & ".xls", _
            FileFormat:=xlWorkbookNormal
    Next myCell
    
    NewWks.Parent.Close savechanges:=False
    
End Sub



Hypnotic_Monkey_Scratcher wrote:
> 
> Hi there
> 
> I have an excel file full of data (about 1000 rows).  I need to export
> each row into a new workbook (just one row in each workbook, resulting
> in about 1000 files), and then save it so that the filename is the
> same as the data from one of the columns.  E.g. cell C2 = 'A001-07'
> which is what the file should be saved as, and C3 is 'A001-08' which
> is what that row's file should be saved as....and so on....
> 
> I normally use VBA in access not excel and while I know what I want
> todo, I am having a bit of trouble!  I guess I will have to put some
> kind of a loop in for each row?
> 
> Any help and suggestions would be very much appreciated.
> 
> Thanks in advance
> 
> Andy

-- 

Dave Peterson
0
petersod (12005)
7/10/2009 7:29:58 PM
Reply:

Similar Artilces:

How do I determine the number of cell formats in this workbook
How do I determine the number of cell formats in this workbook I have an Excel addin that performs that task. Email me for it at ng@officearticles.com ************ Anne Troy www.OfficeArticles.com "tucsonmissiledude" <tucsonmissiledude@discussions.microsoft.com> wrote in message news:C4A64A8E-BACC-434C-A0A1-AC6819EA5694@microsoft.com... > How do I determine the number of cell formats in this workbook Hi TucsonMissileDude, Try the following code from Leo Heuser. To delete unused formats, answer 'yes' to the imitial message box; ansewring 'no' will re...

Error when add new user #4
Hi, dont know when it happened. When I add new user to production MSCRM 4, there is an error message (An error Has occurred.....). However, when I try to add this user to my test CRM, it can successfully add without any problem. Both servers are running the same spec and version. I tried to add different users to production but all failed. I have set the deverror to ON but the error message still appear as An error has occurs. Apart from that, I have used CRM diagostic Tool but cannot see anything unusual from the log. I have even run the repair but effort is all wasted. Please He...

Click and Add new order year to all customers?
I have a main form with customer info. A subform below has a yearly record of the progression of the order. I would like to click a button and instantly add a new year record for each customer. I currently have it set up that the user has to look up each customer individually and go down to the subform click the combo and select order year. How can I do this with one click instead? Thanks Dustin Execute an append query statement to add the records. This example adds a record to tblOrderInfo for each customer in your tblCustomer table, using the year of today's date: Dim strSql ...

Totalling workbooks
I am creating a summary worksheet and i was wondering if it is possible to add values together from different workbooks and then have it automatically update when i create a new workbook, the workbooks being named week 16, week 17 and so on in number order. So that when i create week 18, the figures get included into the summary Try the method described here: http://www.officearticles.com/excel/sum_the_same_cell_in_multiple_microsoft_excel_worksheets.htm ******************* ~Anne Troy www.OfficeArticles.com "blummincars" <blummincars@discussions.microsoft.com> wrote i...

Protecting cells in a shared workbook using autofilters
I created a shared workbook for others to make changes to. I have created an autofilter and have my coworkers only make changes to data under their name. How can I prevent my coworkers from access the other information in the shared workbook? Stuff that they are not suppose to change. In other words can I protect certain cells while autofilter is on? -Stef If the workbook were not shared, then you could protect the worksheet (using VBA) and allow the users to have access to the filters. And this code would have to protect the worksheet each time the workbook opened. But once you sha...

Income Tax using US Tax System Excel WorkBook
Hello, I am looking for Excel Template to calculate Tax using US tax brackets. Can anyone please post a link for it. I have looked up cannot find it on web. Someone got it? Please post a link or webaddress if you got any idea. Thanks. Here's a couple of possibilities (?) http://www.dotxls.com/free-templates/ http://www.dotxls.com/free-templates/165/free-2007-excel-tax-forms-for-april-2008-filing "BetaDocuments" wrote: > Hello, I am looking for Excel Template to calculate Tax using US tax > brackets. Can anyone please post a link for it. I have looked up > ca...

Excel VBA
Hi. Is it possible to use VBA in one workbook to copy a worksheet fro another workbook into another different workbook? If so....how? Thank -- Message posted from http://www.ExcelForum.com Hi try recoding a macro while doing this manually. And yes it is possible :-) -- Regards Frank Kabel Frankfurt, Germany "cjh1984 >" <<cjh1984.16x63k@excelforum-nospam.com> schrieb im Newsbeitrag news:cjh1984.16x63k@excelforum-nospam.com... > Hi. > > Is it possible to use VBA in one workbook to copy a worksheet from > another workbook into another different workbook? ...

notification when new mail arrive if outlook is not active
Is there possibility to have notification when new mail arrive, even outlook is not open >-----Original Message----- >Is there possibility to have notification when new mail >arrive, even outlook is not open >. > To set up a notification, go to Tools --> Options --> Email Options and check the box 'Display a notification message when new email arrives'. This can only work if Outlook is open. I now that but I need notification if Outlook is not started!!! >-----Original Message----- > >>-----Original Message----- >>Is there possibility...

New "Card Vault" retrieves card numbers in version 1.3
We have released a very useful application that I feel anyone could benefit from. With version 1.3 the credit cards are all masked, there is no way around this. They are also deleted from the batch table after the settlement so they cannot be retrieved at a future time. Before version 1.2 if there was a problem settling the batch you could just change the status in the visanetauthorization table and re-batch, this is not possible with version 1.3. If this ever happens with the new version you have to call the bank and they hopefully have the credit card number, you then have to re-ring in a tr...

Showing Duplicate Rows
Hi All, I have seen the question with that same title however that is not exactly "showing" duplicates. It is more showing unique (not duplicate) I would like to truly show the duplicates. For instance there are several duplicate product numbers that are in my sheet and I want to display all the duplicates so that I can make corrections. IE: Item number 31323 31324 31325 31326 31326 31327 31328 31328 31329 31330 31331 How can I filter so that excel will only show those rows that have duplicate Item numbers? Thanks Eagle Use data>filter>autofilter>filter by column...

Addins attached only to selected workbook(s)??
Using Excel 2003 I have written a set of macros that is used with workbooks with a lot of hand entered data. Right now the macros need frequent updates as more features are added. To facilitate providing updates, I felt it was necessary to separate the code from the data so that the user didn't have to do anything when the code was upgraded. To accomplish this, I created an addin containing the macros. Now I just email the new addin and users are good to go. The problem is that the addin is automatically attached to every workbook I open and there are a number of reasons that ...

'07 Large Operation Error (Delete / Insert Rows and/or Columns)
Help! I was so excited to have '07 until... I have a very simple worksheet in a work book - This very simple worksheet takes up 78 rows and 16 columns. Minimals formats / formulas etc. I would like to insert additional rows but I get the error message which says "Large Operation. The operation you are about to perform affects a large # of cells and may take a significant amount of time to complete. Are you sure you want to continue? Note: This operation will be performed in 60 seconds if no response is received". I have tried to select "OK" as well a...

New 75GB Database limit not recognized
I installed Exchange 2003 sp2 a few months ago. I upped the limit to 75GB. Our database was at 20GB and life was fine. A few days ago, Symantec Mail Security broke. I had to uninstall and re-install. Now, the 1216 message in the event log says my limit is 18GB and it dismounts my store every 48 hours or so. The registry changes haven't changed. What gives? Have you tried to reapply Exchange Service Pack 2? Chris <google@dwightrjohnson.com> wrote in message news:1158764967.787848.326410@i42g2000cwa.googlegroups.com... >I installed Exchange 2003 sp2 a few months ago. > I u...

increase total rows
Hi, can someone show me how to increase the maximum number of rows, more than 65,536? thanks Stan I presume you have Excel 2007? Open your .xls file in Excel 2007, save the file as an Excel 2007 file type (.xlsw, .xlsx, etc) using File Save As. Close the file and reopen it you should now have 2007's 1M+ rows available. "Stan" wrote: > Hi, > can someone show me how to increase the maximum number of rows, more than > 65,536? > thanks > Stan Thanks!!! "JLatham" wrote: > I presume you have Excel 2007? > > Open ...

"No more new fonts may be applied to this workbook"
I have an annoying problem that apparently has to do with the number of charts in my workbook. I am using Excel 2002 SP-1. I have a workbook that consists of 7 worksheets and is only 589 KB but I have a large number of charts. As I developed this workbook and the number of charts started to increase I started to get the error message, "No more new fonts may be applied to this workbook" even though I was not adding any new fonts. This usually happened when I tried to change the chart title. I would just click through several of these messages and everything would be OK. Now I cannot...

Export from a form
I know there are several ways to programatically export, like docmd.transferspreadsheet, but I want to export data from a form. I however do not want the file button available to my users. I thought this might work DoCmd.DoMenuItem , acfilemenu, acExport, , acMenuVer70 but it did not. Basically all I want to do is have a command button act like what happens when you click file, export. I want the user to click the button, and then select the location and the format to export to. Am I on the correct track with the DoMenuItem? Nevermind, I figured it out. I guess it helps to keep rea...

Print workbook macro w/o first sheets....
I need to print a workbook with a variable number of worksheets. However, I don't need the first 3 worksheets of the book. It also needs to be collated. I need to create a macro or VB loop to make it automatic from a command button. Any ideas? Thanks in advance, Josh ...

How do I insert a row of blank cells?
I need to know how to insert a row of blank cells every other row in the columns from F to I ONLY!!! I currently have just a straight set of data in those columns like data-data-data-data-data-data. I need to have it alternate data-blankrow-data-blankrow-data-blankrow- as I go down from row to row. I need to do this for about 1000 rows so I need a quick way to do it if there is one. HELP!!! Looks like this: Data Data Data Data I want this: Data Data Data Data Thanks. Chris -open the VB editor -double click the sheet of interest -View from the menu--> Code -Paste the belo...

email by rows
hello All: I'm looking for a macro that will go down a sheet and look for th email address and mail every thing in that row to *ONLY* that person n matter if it has 10 columns or 20 columns I have a sheet set up lik this Employee Name LAB Degree Section E-Mail Address paper1 paper2 paper3 I will be adding more columns as the year goes on that is why it nee to be able to send different size rows. Any help would be appreciated Dal -- Message posted from http://www.ExcelForum.com Hi DaleL Which mail program do you use -- Regards Ron de Bruin http://www.rondebruin.nl "D...

Exporting Extender Data
Does anyone know which table holds the extender data? I tried searching customersource for some sort of extender table structure, but I couldn't find anything. I want to be able to export this data using crystal reports, but I just need to know how to connect to it. Try Techknowledge #899573 "ConnollyandCo" wrote: > Does anyone know which table holds the extender data? I tried searching > customersource for some sort of extender table structure, but I couldn't find > anything. I want to be able to export this data using crystal reports, but I > just n...

How do i recover my lost password of workbook?
I have lost my passwrd of an Excel workbook. Now i need to recover the password or the data of that work book. so what should i do now to recover it. Good morning Saiful Islam Rana You've not said what sort of password you need : If you cannot open your workbook at all: http://www.cpearson.com/excel/password.htm http://www.j-walk.com/ss/excel/links/pword.htm This one however, is but has a few limitations and is well worth trying before you start paying for the service. http://www.download.com/Free-Word-Ex...-10249516.html If you want to remove workbbok and worksheet protection the...

Money won't update w/ bank's new sign in screens
I have accounts at two banks and was trying to set them up in Money 2006 to download automatically because they are both setup in the system. Accounts at one bank works just fine as expected. This bank has a normal signin process where I just log on with my username and passwords and that's it. The other bank, on their own website, has a 2 page login procedure with the picture verification thing so I know I'm on the right site. Money can't log in to this bank to get my account information. Does Money not support this type of new login procedures or something? Do I need ...

IE8
I just updated to IE8 (as IE7 was giving me some trouble) but when I right-click a link there is no option avaliable to "open link in new tab". Help ? I have tried reregistering one of the registry files that some forums say is behind the reason for this problem, but to no avail. However, I think it might be a compatability issue somewhere, as when I turn off add-ons the option reappears. Any suggestions as to what to do ? As manually trying each add-on is going to take forever ! This is really annoying me, as I use the new tabs option all the time whilst work...

New Ethnicity Classifications
Hello: As of 01/01/07, the federal government will be issuing new ethnic classifications. Basically, there will be more ethnic classicfications than what is currently seen in Great Plains Human Resources in the Employee Additional Information Maintenance window for the Ethnic Origin field. Does anyone know if Microsoft will be coming out with an update that lists these additional classifications? Thanks! childofthe1980s ...

New Mailbox Can't Receive External Mail
We have recently added a new mailbox to Exchange 2000 on our company's server. The new user can send and receive internal mail OK and can send external mails also using Outlook 2003. The issue is that he is not receiving mails from external sources although we can tell that they are being sent as we can pick them up from our ISP. We can't find any differences between the new user profile and the previous profiles that work OK. Does anyone have any suggestions as to what we need to do? No-one at my company is that familiar with Exchange. Thanks in advance Pls cancel outlook...