Creating sheets from filter. How to modify to start from row 2.

[Davavo]

Hello all,

Can decipher this impenetrable code sufficiently to make it ignore the first row on the sheet on which it runs?

I am changing the sheet so that the control buttons take up row one.

This sub is called from a larger sub that also changes the button settings so that they are free-floating.

Many thanks in advance.

Option Explicit

Const sname As String = "INVRead" 'change to whatever starting sheet
Const S As String = "J" 'change to whatever criterion column




Sub columntosheetsINV()


Dim wb As Workbook
Dim Sh As Worksheet
Dim TINVRead As TableObject


Dim D As Object, A, CC&
Dim P&, i&, rws&, cls&
Set wb = ThisWorkbook
Set D = CreateObject("scripting.dictionary")


With wb.Sheets(sname)
    rws = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    cls = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    CC = .Columns(S).Column
End With


For Each Sh In Worksheets
    D(Sh.Name) = 1
Next Sh


Application.ScreenUpdating = False
With wb.Sheets.Add(after:=wb.Sheets(sname))
wb.Sheets(sname).Cells(1).Resize(rws, cls).Copy .Cells(1)
.Cells(1).Resize(rws, cls).Sort .Cells(CC), 2, Header:=xlYes
A = .Cells(CC).Resize(rws + 1, 1)
P = 2
For i = 2 To rws + 1
    If A(i, 1) <> A(P, 1) Then
        If D(A(P, 1)) <> 1 Then
            'Sheets.Add.Name = A(P, 1)
            Sheets.Add.Name = ValidWBNameINV(CStr(A(P, 1)))
            .Cells(1).Resize(, cls).Copy Cells(1)
            .Cells(P, 1).Resize(i - P, cls).Copy Cells(2, 1)
        End If
        P = i
    End If
Next i
Application.DisplayAlerts = False
    .Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End With
wb.Sheets(sname).Activate


End Sub

 

[baitmaster]

That code would definitely benefit from being rewritten but it's not quite impenetrable. I'd recommend a rewrite to include better comments, meaningful variable names, and checking of variable types - and I sympathise with anyone that has to read a whole file filled with code like that!

First point - if it ain't broke, do you need to fix it? Whilst you want to add control buttons at the top, do you need to insert a row to do this? Why not just adjust the row height to make space for both buttons and text?

Second, assuming you do go ahead, I think the bit you want to change is just

For i = 2 To rws + 1

You probably just want to change 2 to 3. "rws" is linked to the last found piece of data and so will update automatically

 

[Davavo]

That code would definitely benefit from being rewritten but it's not quite impenetrable. I'd recommend a rewrite to include better comments, meaningful variable names, and checking of variable types - and I sympathise with anyone that has to read a whole file filled with code like that!

First point - if it ain't broke, do you need to fix it? Whilst you want to add control buttons at the top, do you need to insert a row to do this? Why not just adjust the row height to make space for both buttons and text?

Second, assuming you do go ahead, I think the bit you want to change is just

For i = 2 To rws + 1

You probably just want to change 2 to 3. "rws" is linked to the last found piece of data and so will update automatically

thanks for your help baitmaster.
You are right, it think, i should just add a rows worth of height to row 1 and put the headers on the xlbottom.
The code is not mine btw, i just found it lying around on the web. I couldn't rewrite it if you paid me. This is the first thing i have coded.

I have the sheets set up so that only the columns that are used (in a table) are visible, and each resizes itself in proportion to fit whatever screen it is on.
I am just trying to make the thing look professional but the buttons are not cutting it. At the moment i have one button that calls a user-form with 6 or so commands on it.
If i skip the intermediate click and stick all 6 buttons directly on the sheet, and run the sub that changes their settings from 'move size with cells' to 'free-floating' on each button, then that might do it. But if they are on a big monitor they are are going to all get spaced out. Plus, the columns are different sizes. What i want is all of the buttons on one form that resizes with the width of the screen, and all of the buttons onthe form, resize proportionately so that the 'parent form' has no gaps in it.
I see there are 'group boxes' in the developer insert list. Can they be used for something like this?

 

[baitmaster]

I see there are 'group boxes' in the developer insert list. Can they be used for something like this?

I don't think so. I only ever use the ActiveX version on a pop-up controls form, just for presentation purposes, and I wouldn't ever use this feature directly on a worksheet myself

My advice is simply to keep it simple, and prioritise functionality over prettiness. Is there a benefit to trying to adjust sizes of everything so it fits on exactly one screen? How are you making this adjustment, using coding? Sounds like you're adding complexity for no net gain. If you go down this route you need to consider amending the size of the buttons, the spacing between them, text size, text wrapping, ensuring all text remains visible... this becomes a distraction

If it were me I'd fix the size / shape of the buttons and place them all relative to the top left corner. They won't always span the entire screen width, but so what?

 

[Davavo]

My advice is simply to keep it simple, and prioritise functionality over prettiness. Is there a benefit to trying to adjust sizes of everything so it fits on exactly one screen?

How are you making this adjustment, using coding?

Sub fitwidthINV()

Dim wb As Workbook
Dim ws As Worksheet


Dim totwin, usedwin, usedwidth As Long


Dim Conversion As Variant


Set wb = ThisWorkbook
Set ws = wb.Sheets("INVRead")


Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


totwin = ActiveWindow.width                                                                 'totwin = specific to the screen
usedwin = 0.981 * totwin


msgbox totwin


Conversion = wb.Sheets("INVRead").Range("A1").ColumnWidth / wb.Sheets("INVRead").Range("A1").width


wb.Sheets("INVRead").Unprotect Password:="****"


With Sheets("INVRead")


    Columns("A").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("A3").Value * Conversion
    Columns("B").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("B3").Value * Conversion
    Columns("C").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("C3").Value * Conversion
    Columns("D").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("D3").Value * Conversion
    Columns("E").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("E3").Value * Conversion
    Columns("F").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("F3").Value * Conversion
    Columns("G").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("G3").Value * Conversion
    Columns("H").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("H3").Value * Conversion
    Columns("I").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("I3").Value * Conversion
    Columns("J").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("J3").Value * Conversion
    Columns("K").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("K3").Value * Conversion
    Columns("L").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("L3").Value * Conversion
    Columns("M").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("M3").Value * Conversion
    Columns("N").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("N3").Value * Conversion
    Columns("O").ColumnWidth = usedwin * wb.Sheets("ColFactors").Range("O3").Value * Conversion


End With
Sheets("INVRead").Activate
Range("A:O").Select 'set range zoom
ActiveWindow.Zoom = True
Range("B2").Select


Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
wb.Sheets("INVRead").Protect Password:="****"
End Sub

Sounds like you're adding complexity for no net gain.

quite possibly

If you go down this route you need to consider amending the size of the buttons, the spacing between them, text size, text wrapping, ensuring all text remains visible.

that is too much hassle

If it were me I'd fix the size / shape of the buttons and place them all relative to the top left corner. They won't always span the entire screen width, but so what?

I will just stick to the one button that activates the userform and put it up in the top left.

Shame, there should be a better way.

Visual Studio?