asked on

Simple Excel VBA

Hi Experts,

I need to add more line in my existing code. which sort Sheets A - Z between Sheet Start - Finish.

In Code After this line

Set wbData = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Data.xlsx")

Open in new window

I want it Sort sheets A - Z which is between Sheet "Start" & "Finish".

After this line

Set wbResult = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Result.xlsx")

Open in new window

I want it Sort sheets A - Z which is between Sheet "Start" & "Finish".

& Same way before closing for both the WB i.e.

 wbData.Close False
    wbResult.Close False

Open in new window

Here is the fore Code

Sub Demo()

    Dim wbCurrent As Workbook, wbData As Workbook, wbResult As Workbook

    Set wbCurrent = ActiveWorkbook
    Set wbData = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Data.xlsx")
    Set wbResult = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Result.xlsx")

    wbData.Activate

    If wbData.Sheets(1).Name = wbResult.Sheets(1).Name Then
        wbData.Sheets(1).Range("A1:F" & Range("F1048576").End(xlUp).Row).Copy
        wbCurrent.Activate
        Range("A1").PasteSpecial

        wbResult.Activate
        wbResult.Sheets(1).Range("A3:C3").Copy
        wbCurrent.Activate
        Range("L3:N3").PasteSpecial
    Else
        wbData.Sheets(1).Range("A1:F" & Range("F1048576").End(xlUp).Row).Copy
        wbCurrent.Activate
        Range("A1").PasteSpecial

        varData = Split(InputBox("Please enter value for L3:N3, seperated by a space.", "Data input"), " ")

        wbCurrent.Sheets(1).Range("L3").Value = varData(0)
        wbCurrent.Sheets(1).Range("M3").Value = varData(1)
        wbCurrent.Sheets(1).Range("N3").Value = varData(2)

    End If

    wbData.Close False
    wbResult.Close False

End Sub

Open in new window

Thank You

Joe Howard

I'm not sure I understand what you're trying to accomplish here.

I want it Sort sheets A - Z which is between Sheet "Start" & "Finish".

???

Please explain what you want in simple terms, if possible supply a sample that will show the original data and what it should look like after the sorting.

Naresh Patel

ASKER

Here it is all 3 Files.
Process.xlsm
Data.xlsx
Result.xlsx

Naresh Patel

ASKER

in Data WB & Result WB there is 1st sheet is "Ticker" & after that Sheet "Start" & last is "Finish". I want to sort A - Z sheets which is between Sheet "Start" - "Finish" as mention in my post.

Thanks

Naresh Patel

ASKER

in short 4 time sorting after - opening there two WB & Before closing these two WB.

Thanks

Joe Howard

Try this:

Sub Demo()

    Dim wbCurrent As Workbook, wbData As Workbook, wbResult As Workbook

    Set wbCurrent = ActiveWorkbook
    Set wbData = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Data.xlsx")
    SortSheets wbData
    Set wbResult = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Result.xlsx")
    SortSheets wbResult

    wbData.Activate

    If wbData.Sheets(1).Name = wbResult.Sheets(1).Name Then
        wbData.Sheets(1).Range("A1:F" & Range("F1048576").End(xlUp).Row).Copy
        wbCurrent.Activate
        Range("A1").PasteSpecial

        wbResult.Activate
        wbResult.Sheets(1).Range("A3:C3").Copy
        wbCurrent.Activate
        Range("L3:N3").PasteSpecial
    Else
        wbData.Sheets(1).Range("A1:F" & Range("F1048576").End(xlUp).Row).Copy
        wbCurrent.Activate
        Range("A1").PasteSpecial

        varData = Split(InputBox("Please enter value for L3:N3, seperated by a space.", "Data input"), " ")

        wbCurrent.Sheets(1).Range("L3").Value = varData(0)
        wbCurrent.Sheets(1).Range("M3").Value = varData(1)
        wbCurrent.Sheets(1).Range("N3").Value = varData(2)
    End If
    
    SortSheets wbData
    SortSheets wbResult

    wbData.Close True
    wbResult.Close True

End Sub

Sub SortSheets(wb As Workbook)
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Index > 2 And ws.Index < ActiveWorkbook.Worksheets.Count Then
            With ws.Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("A3:A" & Range("A1048576").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SetRange Range("A2:F" & Range("F1048576").End(xlUp).Row)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
    Next ws
End Sub

Open in new window

Naresh Patel

ASKER

it is not sorting Sheets between Sheet "Start" - "Finish".

Thanks

Naresh Patel

ASKER

i.e sorting mean this

From this

Thanks

Joe Howard

Oh, I though you wanted the data on sheets sorted!

Naresh Patel

ASKER

I have little cue how to do that as I have one code which perform this kind of thing but I don't know how to apply to this code. just take a look.

Sub XBuild()
Dim wsX As Worksheet, wsRebuild As Worksheet, wsCalc As Worksheet
Dim cel As Range, cel2 As Range, rg As Range
Dim i As Long, j As Long, n As Long
Dim k As Integer, kk As Integer, Start As Integer, Finish As Integer
Dim v As Variant
Start = Worksheets("Start").Index
Finish = Worksheets("Finish").Index
Set wsRebuild = Worksheets("Rebuild")
Set wsCalc = Worksheets("Calculation")

For k = Start + 1 To Finish - 1
    With Worksheets(k)

Open in new window

If (Finish - Start) > 2 Then
    For kk = Start + 1 To Finish - 2
        For k = Start + 1 To Finish - 2
            If Worksheets(k + 1).Name < Worksheets(k).Name Then Worksheets(k + 1).Move before:=Worksheets(k)
        Next
    Next
End If
End Sub

Open in new window

this is only part of code.

Thanks

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naresh Patel

ASKER

it is sorting now but one issue of previous procedure - asking for Input box GOOD
but copy to A:F data absent.

but I guess in my next question this going to solve. may I ask my next question?

Naresh Patel

ASKER

Thank You

Joe Howard

Go ahead!

Naresh Patel

ASKER

ok & now only 2 question left so pls be with me.

Thanks

Naresh Patel

ASKER

This is the One.

Thanks

Naresh Patel

ASKER

Any luck for new question?

Naresh Patel

ASKER

As you know fully how code flow I had put short description so I guess no one understand what I am after to achieve. so please request to you just confirm you are on that question else I need to modify the comments to every one understand.

I am not hurry.

thanks