Creating different workbooks for data in a workbook

[rohini21]

Dear all,

I have a mission of creating different workbooks from one workbook. The single workbook contains the data of 250 clients. What is needed is I should be able to sort the data for each client and then create seperate workbook for each client containing the sorted data.

Please help.

Cheers
Vivek

 

[SkipVought]

Vivek,

Tell me, then, that each of these separate wrokbooks is going to be sent in separate directions to different recipients, and that you are NOT going to maintain separate client workbooks containing similar data.

So you sort your table by client and write code to loop thru your table and copy and paste client chunks into a new workbook. If you are not familiar with VBA in Excel, then actually macro record creating a new workbook, naming it, copying and pasting a client chunk, save and closeing the new workbook. The trickiest part for you might be defining each client chunk for the copy and paste. But lots of us can help you with that when you get there.

Hope this helps Skip,
Skip@theofficeexperts.com

 

[rohini21]

Thanks for the reply. No I am not mailing the reports but maintaining the records for all clients. Yes I am familiar with VBA. But I am not sure how to loop through so that I can select the data of one client and paste it in a new workbook and then come back to the original workbook to copy paste the data of other client.

 

[xlbo]

From a slightly different angle - depending on how much data there is, you could always slap a pivot table over the lot. then, make CUSTOMER your page field
Then, right click on the page field and choose "Show Pages" - this'll create a different page for each customer (within the same workbook tho)
but then you can just use
For each ws in thisworkbook.worksheets
ws.copy
activeworkbook.saveas name:=sheets(1).range("A1&quot.text
activeworkbook.close
next Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[SkipVought]

Is there a reason for dividing the data into workbooks by client? You are making alot of extra work and potential headachs for maintaining a database of similar data. It's not good database design to make similar tables (workbooks or sheets) based on a data value. But if you insist...

If your table of data is contiguous, then you can define the limits of your data area as follows...

Sub CopyClents()
    Dim wbMain As Workbook, wbNew As Workbook
    Dim wsMain As Worksheet, wsNew As Worksheet
    Dim lFirstRow As Long, lLastRow As Long, lRow As Long, lRow1 As Long, lRow2 As Long
    Dim iFirstCol As Integer, iLastCol As Integer, iClientCol As Integer
    Dim sPrevClient As String, sThisClient As String
    
    Set wbMain = ActiveWorkbook
    Set wsMain = ActiveSheet
    With ActiveSheet.UsedRange
        lFirstRow = .Row
        lLastRow = .Rows.Count + lFirstRow - 1
        iFirstCol = .Column
        iLastCol = .Columns.Count + iFirstCol - 1
    End With
    iClientCol = 1
    lRow1 = lFirstRow
    For lRow = lFirstRow To lLastRow
        wbMain.Activate
        sThisClient = Cells(lRow, iClientCol).Value
        If sThisClient <> sPrevClient And lRow > lFirstRow Then
            lRow2 = lRow - 1
            Workbooks.Add
            Set wbNew = ActiveWorkbook
            Set wsMain = ActiveSheet
            wbMain.wsMain.Range(Cells(lRow1, iFirstCol), Cells(lRow1, iLastCol)).Copy _
                Destination:=wbNew.wsNew.Cells(1, 1)
            wbMain.wsMain.Range(Cells(lRow1, iFirstCol), Cells(lRow2, iLastCol)).Copy _
                Destination:=wbNew.wsNew.Cells(2, 1)
            wbNew.SaveAs sNewFileName
            wbNew.Close
            lRow1 = lRow
        End If
        sPrevClient = sThisClient
    Next
End Sub

hope this helps Skip,
Skip@theofficeexperts.com

 

[rohini21]

Thanks a lot for the replies. I am overwhelmed with response I got. I do understand your concerns over maintaing the similar database. Actually I am working for Finance Management Research Center who has a database of all their clients and they want to sort the data for individual client so that they can provide them the respective data... Thats why this is going on. Anyway I figured out the way t o create workbooks.
I have a problem in error trapping. I am dynamically generating the filenames and savincthem using saveAs. Since everytime the filenames created are same there is a pop up window asking to overwrite or not... The "yes" button works fine for me. But when I click 'no" or "cancel" I get error. Please help... If you wish I can put up my code here

 

[RobBroekhuis]

When you click no or cancel, the saveas command fails - and the VBA throws an error. You need to decide which action is appropriate when the user chooses no. Or, alternatively, don't give the user the option, by setting

application.displayalerts=false
..saveas command here
application.displayalerts=true

If you need to have the freedom to say no, then use error handling, for example:

on error resume next
SaveSuccess=false
do while not SaveSuccess
activeworkbook.saveas strFileName
if err<>0 then
err.clear
strFileName=application.getsaveasfilename(strFilename, _
&quot;Excel workbooks (*.xls),*.xls&quot;, 1, _
&quot;Enter new filename for data&quot
if strFileName=&quot;False&quot; then
(User REALLY doesn't want to save!)
end if
else
SaveSuccess=true
end if
loop

Rob

 

[SkipVought]

I am relieved, as you, too, ought to be, that these client workbooks will not have to be maintained. Skip,
Skip@theofficeexperts.com