VBA Code to Sort Data from one Worksheet to Multiple Worksheets

[satyavati0120]

Hi,
Please find below the snapshot of my excel sheet & description:


Sheet Description

• (Column A) and VA rating (Column B) information is added by the user.
• Then the user Clicks on Sort button which arranges the table in Descending order based on VA ratings.
• Then the user clicks on CREATE TRANSFOMRER Button, which creates the necessary number of tabs for Transformers.
• After this Once the user clicks DISTRIBUTE VA LOAD Button, the Table will be split into Individual Transformer Sheets Table upto 85VA; Table header should also be copied from the Main Sheet(Sheet1)

Having problem with VBA code for the DISTRIBUTE VA LOAD, cannot figure out what code to write.

Please can someone advise? Very limited coding understanding on my part.

Regards,
Satyajit

 

[Marc L]

Hi, that needs an unique button as all can be done at once when splitting the data to individual sheets​

but you forgot to attach the exact expected result …​

​

 

[satyavati0120]

Hi Marc,

Thanks for your reply.

I understand what you mean about having one consolidated button to do it all.

But as my coding skills are extremely limited, I could not club the button function into one.
(Just tried one button for one step to make it easier on me)

I have attached an updated excel file (which shows the expected results in Transformer1, Transformer2, Transformer3 Worksheets, this is the result that i am trying to generate after Button 3 is pressed in Sheet1)

Please advise.

Regards,
Satyajit

 

[Marc L]

According to your last attachment an all-at-once single button VBA demonstration for starters (v2)​

creating the 'Transformer' sheets after the source data 'Sheet1' :​

Sub Demo1()
     Dim V, W, X, N%, L&, R&
         V = [{"Total VA Used",0;"Free VA Available",0}]
    With Application
        .DisplayAlerts = False
         While Sheets.Count > Sheet1.Index:  Sheets(Sheets.Count).Delete:  Wend
        .ScreenUpdating = False
         Sheet1.ListObjects(1).Sort.SortFields.Clear
    With Sheet1.ListObjects(1).Range.Rows
        .Sort .Cells(2), 2, Header:=1
        W = .Cells(2, 3)
        X = .Columns(2)
    For N = 1 To .Cells(5, 3)
            Sheets.Add(, Sheets(Sheets.Count)).Name = "Transformer #" & N
            [A1].ColumnWidth = .Cells(1).ColumnWidth
           .Item(1).Copy [A1]
            L = 1
        For R = 2 To .Count
            If X(R, 1) + V(1, 2) <= W And X(R, 1) Then
                L = L + 1
                V(1, 2) = V(1, 2) + X(R, 1)
                X(R, 1) = 0
               .Item(R).Copy Cells(L, 1)
                If V(1, 2) = W Then Exit For
            End If
        Next
             V(2, 2) = W - V(1, 2)
        With Cells(L, 1)(2).Resize(2, 2)
             Range("A1,B1:B" & L & "," & .Address).HorizontalAlignment = xlCenter
            .Borders.Weight = 2
            .Rows(1).Interior.ColorIndex = 20
            .Rows(2).Interior.ColorIndex = 19
            .Value = V
        End With
             V(1, 2) = 0
    Next
    End With
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[satyavati0120]

Thanks Marc!!!!!

The code works.

Works like magic!!!!

 

[satyavati0120]

@Marc L Can the all-at-once single button also add a Treemap on each transformer tab?
I have added an updated sheet with your code & in the Transformer Tabs i have manually created the expected Charts
How can that be added to this unique button?

Regards,
Satyajit

 

[Marc L]

The Excel version on my old tests laptop can't display your charts so I'm off for this part​

but another helper may give it a try …​

​

 

[satyavati0120]

The Excel version on my old tests laptop can't display your charts so I'm off for this part​

but another helper may give it a try …​

​

No problem Marc. Really appreciate your help..