• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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
0
Naresh Patel
Asked:
Naresh Patel
  • 12
  • 5
1 Solution
 
MacroShadowCommented:
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.
0
 
Naresh PatelTraderAuthor Commented:
Here it is all 3 Files.
Process.xlsm
Data.xlsx
Result.xlsx
0
 
Naresh PatelTraderAuthor Commented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Thanks
0
 
MacroShadowCommented:
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

0
 
Naresh PatelTraderAuthor Commented:
it is not sorting Sheets between Sheet "Start" - "Finish".

Thanks
0
 
Naresh PatelTraderAuthor Commented:
i.e sorting mean this

From this Before SortAfter Sort
Thanks
0
 
MacroShadowCommented:
Oh, I though you wanted the data on sheets sorted!
0
 
Naresh PatelTraderAuthor Commented:
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
0
 
MacroShadowCommented:
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")
    wbData.Activate
    SortWorksheets
    Set wbResult = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Result.xlsx")
    wbResult.Activate
    SortWorksheets

    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.Activate
    SortWorksheets
    wbResult.Activate
    SortWorksheets

    wbData.Close True
    wbResult.Close True

End Sub

Private Sub SortWorksheets()
     
    Dim N As Integer
    Dim M As Integer
    Dim FirstWSToSort As Integer
    Dim LastWSToSort As Integer
    Dim SortDescending As Boolean
     
    SortDescending = False
     
    If ActiveWindow.SelectedSheets.Count = 1 Then
        FirstWSToSort = 3
        LastWSToSort = Worksheets.Count - 1
    Else
        With ActiveWindow.SelectedSheets
            For N = 2 To .Count
                If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                    MsgBox "You cannot sort non-adjacent sheets"
                    Exit Sub
                End If
            Next N
            FirstWSToSort = .Item(1).Index
            LastWSToSort = .Item(.Count).Index
        End With
    End If
     
    For M = FirstWSToSort To LastWSToSort
        For N = M To LastWSToSort
            If SortDescending = True Then
                If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
                    Worksheets(N).Move Before:=Worksheets(M)
                End If
            Else
                If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
                    Worksheets(N).Move Before:=Worksheets(M)
                End If
            End If
        Next N
    Next M
     
End Sub

Open in new window

0
 
Naresh PatelTraderAuthor Commented:
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?
0
 
Naresh PatelTraderAuthor Commented:
Thank You
0
 
MacroShadowCommented:
Go ahead!
0
 
Naresh PatelTraderAuthor Commented:
ok & now only 2 question left so pls be with me.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
This is the One.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
Any luck for new question?
0
 
Naresh PatelTraderAuthor Commented:
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now