VBA split data as worksheets alphabetically, save tabs as workbooks, set print area.

T

TxRookie

New Member
Joined
Apr 24, 2017
Messages
1
I have a macro that currently works for me (after much help from others in another forum) but I would like some help tweaking it.
Please note that the subtotal function excludes the first sheet because doing so isn't necessary and will cause excel to crash due to #of rows.



1. The data is split into worksheets by column A (Invoice #) but it isn't done in alphabetical order, even if the first sheet is in alphabetical order. How can I change that?

2. How can I save each tab as a separate Excel 2013 file without deleting it in the original sheet?

3. I have a print area set that includes all columns but I would like to auto detect how many pages are needed. The limit of rows I would like to have in one page is 75. For ex, an invoice with 230+ rows would require 4 sheets.

Sample Data
[TABLE="width: 1516"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Invoice[/TD]
[TD]Descr[/TD]
[TD]Customer[/TD]
[TD]Acctg Date[/TD]
[TD]AP Unit[/TD]
[TD]Voucher[/TD]
[TD]Line[/TD]
[TD]Ship To[/TD]
[TD]Ult Use Code[/TD]
[TD]Code[/TD]
[TD]Vendor[/TD]
[TD]Name[/TD]
[TD]Invoice[/TD]
[TD]GL Unit[/TD]
[TD]Entity[/TD]
[TD]Account[/TD]
[TD]Dept[/TD]
[TD]Project[/TD]
[TD]Activity[/TD]
[TD]Source Type[/TD]
[TD]St[/TD]
[TD]Taxable[/TD]
[TD]PO No.[/TD]
[TD]PO Unit[/TD]
[TD] Amount [/TD]
[TD] Tax [/TD]
[TD] Percent [/TD]
[TD] ID [/TD]
[TD]Source Descr[/TD]
[/TR]
[TR]
[TD]ABC-0000001357[/TD]
[TD]TX[/TD]
[TD]000000000000797[/TD]
[TD="align: right"]3/23/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497649[/TD]
[TD="align: right"]1[/TD]
[TD]TX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]325784[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000056[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]NY[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 3,962.00[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]Non POVchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001360[/TD]
[TD]CA[/TD]
[TD]000000000000821[/TD]
[TD="align: right"]3/29/2017[/TD]
[TD]XXXXX[/TD]
[TD]00498828[/TD]
[TD="align: right"]1[/TD]
[TD]CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1009091[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000060[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]CA[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 3,300.00[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001361[/TD]
[TD]CA[/TD]
[TD]000000000000850[/TD]
[TD="align: right"]3/31/2017[/TD]
[TD]XXXXX[/TD]
[TD]00499344[/TD]
[TD="align: right"]1[/TD]
[TD]CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]325794[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000048[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]CA[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 3,962.00[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001362[/TD]
[TD]TX[/TD]
[TD]000000000002113[/TD]
[TD="align: right"]3/22/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497420[/TD]
[TD="align: right"]12[/TD]
[TD]TX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]93186[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789500[/TD]
[TD]9999[/TD]
[TD]YZ001932[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]TX[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD] 174.25[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]Non POVchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001369[/TD]
[TD]AL[/TD]
[TD]000000000000475[/TD]
[TD="align: right"]3/21/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497403[/TD]
[TD="align: right"]1[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17025[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000024[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]AL[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 5,141.01[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001369[/TD]
[TD]AL[/TD]
[TD]000000000000475[/TD]
[TD="align: right"]3/21/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497403[/TD]
[TD="align: right"]2[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17025[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000024[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]AL[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 752.40[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001369[/TD]
[TD]AL[/TD]
[TD]000000000000475[/TD]
[TD="align: right"]3/21/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497403[/TD]
[TD="align: right"]3[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17025[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000024[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]AL[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 1,777.23[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001369[/TD]
[TD]AL[/TD]
[TD]000000000000475[/TD]
[TD="align: right"]3/21/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497403[/TD]
[TD="align: right"]4[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17025[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000024[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]AL[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 2,322.10[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001369[/TD]
[TD]AL[/TD]
[TD]000000000000475[/TD]
[TD="align: right"]3/21/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497403[/TD]
[TD="align: right"]2[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17025[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000024[/TD]
[TD]FGHIJ[/TD]
[TD]987658[/TD]
[TD]AL[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 752.40[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001369[/TD]
[TD]AL[/TD]
[TD]000000000000475[/TD]
[TD="align: right"]3/21/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497403[/TD]
[TD="align: right"]3[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17025[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000024[/TD]
[TD]FGHIJ[/TD]
[TD]987658[/TD]
[TD]AL[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 1,777.23[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]ABC-0000001369[/TD]
[TD]AL[/TD]
[TD]000000000000475[/TD]
[TD="align: right"]3/21/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497403[/TD]
[TD="align: right"]4[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17025[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789000[/TD]
[TD]9999[/TD]
[TD]AB000024[/TD]
[TD]FGHIJ[/TD]
[TD]987658[/TD]
[TD]AL[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 2,322.10[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]XYZ-0000013783[/TD]
[TD]TX[/TD]
[TD]000000000002014[/TD]
[TD="align: right"]3/24/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497908[/TD]
[TD="align: right"]1[/TD]
[TD]TX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I17017[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789500[/TD]
[TD]9999[/TD]
[TD]YZ002596[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]PA[/TD]
[TD]T[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 8,712.00[/TD]
[TD] 522.72[/TD]
[TD] 6.00[/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]XYZ-0000013783[/TD]
[TD]TX[/TD]
[TD]000000000002014[/TD]
[TD="align: right"]3/24/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497908[/TD]
[TD="align: right"]2[/TD]
[TD]TX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I17017[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789500[/TD]
[TD]9999[/TD]
[TD]YZ002596[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]PA[/TD]
[TD]T[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 8,712.00[/TD]
[TD] 522.72[/TD]
[TD] 6.00[/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]XYZ-0000013783[/TD]
[TD]TX[/TD]
[TD]000000000002014[/TD]
[TD="align: right"]3/24/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497908[/TD]
[TD="align: right"]3[/TD]
[TD]TX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I17017[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789500[/TD]
[TD]9999[/TD]
[TD]YZ002596[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]PA[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 2,178.00[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]XYZ-0000013783[/TD]
[TD]TX[/TD]
[TD]000000000002014[/TD]
[TD="align: right"]3/24/2017[/TD]
[TD]XXXXX[/TD]
[TD]00497908[/TD]
[TD="align: right"]4[/TD]
[TD]TX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I17017[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789500[/TD]
[TD]9999[/TD]
[TD]YZ002596[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]PA[/TD]
[TD]E[/TD]
[TD]20000XXXXX[/TD]
[TD]12345[/TD]
[TD] 2,178.00[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]PO Vchr[/TD]
[/TR]
[TR]
[TD]XYZ-0000013784[/TD]
[TD]TX[/TD]
[TD]000000000002014[/TD]
[TD="align: right"]3/30/2017[/TD]
[TD]XXXXX[/TD]
[TD]00498980[/TD]
[TD="align: right"]56[/TD]
[TD]TX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]93290[/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789500[/TD]
[TD]9999[/TD]
[TD]YZ002597[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]PA[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD] 44.17[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]Non POVchr[/TD]
[/TR]
[TR]
[TD]XYZ-0000013784[/TD]
[TD]AL[/TD]
[TD]000000000002014[/TD]
[TD="align: right"]3/21/2017[/TD]
[TD]ZZZZZ[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12345[/TD]
[TD]ABCDEFG[/TD]
[TD]789500[/TD]
[TD]9999[/TD]
[TD]YZ002597[/TD]
[TD]FGHIJ[/TD]
[TD]987654[/TD]
[TD]PA[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD] 1,200.00[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] [/TD]
[TD]INV[/TD]
[/TR]
</tbody>[/TABLE]



Thanks in advance for your help.




Code: 
Sub Invoice()
 
 
Dim ws As Worksheet, a, e, dic As Object
    
 
      Cells.Select
              With Selection.Font
                    .Name = "Arial Narrow"
                    .Size = 10
                    Rows("1:10000").RowHeight = 15
                    Columns("K:L").Select
                    Selection.Style = "Comma"
                               
End With
 
 ActiveSheet.Range("a1:W1").Select
 Selection.Copy
 On Error Resume Next
 Application.ScreenUpdating = False
 For Each ws In Worksheets
 ws.Columns("A:W").Sort Key1:=ws.Columns("J"), Order1:=xlDescending, Key2:=ws.Columns("O"),   Order2:=xlAscending
   Next ws
   ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll
   Application.ScreenUpdating = True
 
    Application.ScreenUpdating = False
    Set dic = CreateObject("Scripting.Dictionary")
    With Sheets("sheet1").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        a = .Columns(1).Offset(1).Resize(.Rows.Count - 1).Value
        For Each e In a
            If Not dic.exists(e) Then
                dic(e) = Empty
                If Not Evaluate("isref('" & e & "'!a1)") Then
                    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = e
                End If
                Sheets(e).Cells.Clear
                .AutoFilter 1, e
                .Copy Sheets(e).Cells(1)
                With Sheets(e).Cells(1).CurrentRegion
                  
 .Subtotal GroupBy:=10, Function:=xlSum, TotalList:=Array(11, 12)
               .Parent.Cells.ClearOutline
                .Columns.AutoFit
                .PageSetup.PrintTitleRows = "$1:$1"
If .Rows.Count > 75 Then
                For i = 76 To .Rows.Count Step 75
                .Parent.HPageBreaks.Add before:=.Rows(i)
                        Next
                End If
                                With .Parent.PageSetup
                                .Orientation = xlLandscape
                                .FitToPagesWide = 1  '? ---- changed
                                .Zoom = False
End With
                                 .AutoFilter
        
                End With
                                 Application.ScreenUpdating = True
 
                End If
 Next
                End With
 
End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
You must log in or register to reply here.

Similar threads

R
  • Solved
VBA Help - Split Data Worksheet with helper sheet into separate workbooks, preserve all formatting and structure and PW protect sheet
Replies
6
Views
352
John_w
J
E
  • Solved
VBA Find match, if found, copy rows and paste in different workbook
Replies
6
Views
461
MARK858
MARK858
S
  • Solved
VBA code to transpose rows to columns and copy data
Replies
4
Views
594
Mario_R
M
D
  • Question Question
Find the matching details from 2 workbooks compile it in a sheet and then summarize it and put it in different sheet.
Replies
0
Views
381
deba2020
D
H
  • Solved
compare two workbooks and add relevant data to one workbook
Replies
6
Views
646
Halley yenn
H

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top