• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split data into multiple worksheets and return total on each worksheet

shili12

Member
Apparently , my objective is to take post all creditors (payee column) into different worksheets, and return a total of payments made to them:-
67881
To return A Ltd with total on worksheet created and named A Ltd: 532,000.00, to have worksheet called C Ltd: 380,000.00, etc. see attachment.
Apparently having problem with code:-

Code:
Sub Splitdatatosheets()
'
' Splitdatatosheets Macro
'

'
Dim rng As Range

Dim rng1 As Range

Dim vrb As Boolean

Dim sht As Worksheet

Set rng = Sheets("Sheet1").Range("b2")

Set rng1 = Sheets("Sheet1").Range("A2:f2")

vrb = False


Do While rng <> ""

    For Each sht In Worksheets
    
        If sht.Name = Left(rng.Value, 100) Then
        
            sht.Select
              
            Range("A1").Select
              
            Do While Selection <> ""
              
                ActiveCell.Offset(1, 0).Activate
                    
            Loop
                    
            rng1.Copy ActiveCell
                
            ActiveCell.Offset(1, 0).Activate
                
            Set rng1 = rng1.Offset(1, 0)
                
            Set rng = rng.Offset(1, 0)
                
            vrb = True
            
        End If

    Next sht
    
    If vrb = False Then

    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Left(rng.Value, 100)

    Sheets("Sheet1").Range("A1:F1").Copy ActiveSheet.Range("A1")

    Range("B2").Select
              
    Do While Selection <> ""
              
        ActiveCell.Offset(1, 0).Activate
                    
    Loop

    rng1.Copy ActiveCell

    Set rng1 = rng1.Offset(1, 0)
    
    Set rng = rng.Offset(1, 0)
    
    End If
    
vrb = False

Loop

End Sub
 

Attachments

Marc L

Excel Ninja
As there is no expected result in the attachment, as you forgot to elaborate it like the problem as well,​
as my guessing time is over, same player shoot again ‼​
 

shili12

Member
To further expound, for sake of clarity after running the macro:-
Worksheet A Ltd will look like:-
67890

Worksheet C Ltd will look like:-
67891

Worksheet E Ltd (one entry only) will look like :-
67892



I have attached how the solution will reach, remember i want all in Payee column to have a separate worksheet,
I hope I have elaborated and expounded it with greater clarity now.
 

Attachments

jolivanes

New Member
Is this what you mean?
Code:
Sub Maybe()
    Dim sh As Worksheet, sh1 As String, c As Range, i As Long
    sh1 = ActiveSheet.Name
    Application.ScreenUpdating = False
    For Each c In Sheets(sh1).Range("B2:B" & Sheets(sh1).Cells(Rows.Count, 2).End(xlUp).Row)
    If c.Value <> "" Then
        If Not Evaluate("isref('" & c.Value & "'!A1)") Then Sheets.Add(, Sheets(Sheets.Count)).Name = c.Value: ActiveSheet.Range("A1:F1").Value = Sheets(sh1).Range("A1:F1").Value
    End If
            Set sh = Sheets(c.Value)
                With Sheets(sh1)
                    c.Offset(, -1).Resize(, 6).Copy sh.Cells(sh.Rows.Count, 1).End(xlUp).Offset(1)
                End With
    Next c
    For i = 3 To ThisWorkbook.Sheets.Count    '<---- Two (2) sheets at the left ("Sheet" and "Sheet1") not to be totalled
        With Worksheets(i)
            .Cells(Rows.Count, 5).End(xlUp).Offset(1).Value = WorksheetFunction.Sum(.Range(.Cells(2, 5), .Cells(.Cells(Rows.Count, 5).End(xlUp).Row, 5)))
        End With
    Next i
    Sheets(sh1).Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
If you're going to use it on the hidden sheet ("Sheet"), you'll have to shorten the names in Column B to Maximum 31 Characters.
31 Characters is the maximum you can use for a sheet name.
 

shili12

Member
Ok, Thanks for tip of 31 characters max required for name worksheet:-
This is error i am getting., which means i have to undergo an exercise of reducing all characters in column Payee to 31 chars max !

Did it, removed all characters (; ' / .) and it worked like a charm !
Thank you !
67900
 
Last edited:

Marc L

Excel Ninja
I have attached how the solution will reach
An easy & fast way is the Excel basics advanced filter like even a beginner can operate manually :​
Code:
Sub Demo1()
        Dim R&, S$
        Application.ScreenUpdating = False
    With Sheet2.[A1].CurrentRegion
           .Columns(2).AdvancedFilter xlFilterCopy, , .Range("L1"), True
        For R = 2 To .Range("L1").End(xlDown).Row
            S = .Cells(R, 12).Text
            If Evaluate("ISREF('" & S & "'!A1)") Then Sheets(S).UsedRange.Clear Else Sheets.Add(, Sheets(Sheets.Count)).Name = S
                .Range("L2") = S
                .AdvancedFilter xlFilterCopy, .Range("L1:L2"), Sheets(S).[A1]
            With Sheets(S).UsedRange.Rows
                If .Count > 2 Then
                   .Cells(.Count + 2, 5).NumberFormatLocal = .Cells(.Count, 5).NumberFormatLocal
                   .Cells(.Count + 2, 4).Resize(, 2) = Array(" Total  :", Application.Sum(.Columns(5)))
                End If
                   .Resize(.Count + 2).Columns.AutoFit
            End With
        Next
           .Range("L1:L" & R).Clear
    End With
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

shili12

Member
@Marc L , Perfect !works exactly like it should, even the word "Total" is placed , although i omitted to place it in my outcome detail.
Your statement :"An easy & fast way is the Excel basics advanced filter like even a beginner can operate manually : "
Would an Advanced filter separate into all those separate worksheets ?

New stuff i learnt: worksheet name can have a max of 31 characters and you're not to use characters {; " ' / & }( [}}
 
Last edited:

Marc L

Excel Ninja
When using an advanced filter you just before place some criteria within some cells in order to extract​
what is needed to a particular destination, so an extraction at a time …​
Imagine if the source table has 50 000 rows and for some reason you can't sort it​
so a filter or better an advanced filter is the way to go.​
So I just reproduce by code what anyone can do manually with some Excel features.​
That's the main VBA coding rule : what Excel offers first ? How should I operate manually ? …​
 
Hi,

I am not sure whether I have understood your requirement. Assuming you want each vendor to get split into different sheets then I would suggest first create a pivot(I have added copy and pasted the payee name again into raw data)--Keep payee name into Filter--Go to Analyze Ribbon--click on tiny black in the right side of option--select show report filter pages

File attached

Hope it helps
 

Attachments

shili12

Member
Hi,

I am not sure whether I have understood your requirement. Assuming you want each vendor to get split into different sheets then I would suggest first create a pivot(I have added copy and pasted the payee name again into raw data)--Keep payee name into Filter--Go to Analyze Ribbon--click on tiny black in the right side of option--select show report filter pages

File attached

Hope it helps
Excellent, exactly as i wanted @neeraj2050 , Question: Did you add sheet name after sheet name after sheet name .......??, or give me some time to figure out yr statement " Keep payee name into Filter--Go to Analyze Ribbon--click on tiny black in the right side of option--select show report filter pages"
I reviewed the workbook and its like as below:-
67957

I have looked at Analyze ribbon, my excel version is 2013 , report filter pages isnt there ?:-
67958:eek:
 
Last edited:
No need to create different sheet manually. Automatically pivot would create separate sheets

That option is available in 2007 as well so don't worry

Just follow steps carefully

Click Analyze ribbon-----into extreme left you will see option button, don't click it but select tiny black in the right end of that button--click report filter pages
 

shili12

Member
@neeraj2050
Thank you, the above is just an example but in reality my data has over 31 characters, which cant accomodate worksheet name.
I managed to locate it(BTW it was on left not extreme right as earlier indicated, i went right=> several times looking for it as per your instructions:-
67969
 
Last edited:

shili12

Member
Fine, as a learning tool, @neeraj2050 , i tried to figure out what you did to reach your stage , meanwhile not discounting the efforts made by @Marc L ,@rahulshewale1 & @jolivanes who had excellent macros, needless to say.
First you added a duplicated column Payee2 to my table., having the same data as payee.
Then after you run the PIVOT (insert PIVOT), after that comes the tricky part, (which i managed to reach after several failed attempts ).
I dragged the Payee to filter, the rest i dragged to Rows, and lastly Amount, i dragged to Sum of Amount.
67970
Then comes the part you omitted to inform me:-
(1) Go to Design=>Report layout=> Show in tabular form,
(2) Go to Subtotals=>Do not show subtotals,

then I finally went to your statement:-
"Just follow steps carefully

Click Analyze ribbon-----into extreme left you will see option button, don't click it but select tiny black in the left end of that button--click report filter pages "


So I am just placing here your omitted parts so as for this to be a comprehensive learning tool.

And as for using your pivot method, this is how those with over 31 characters came out in real workbook.
67971
 
Last edited:
Top