?
Solved

Simple Excel VBA

Posted on 2014-04-03
17
Medium Priority
?
264 Views
Last Modified: 2014-04-03
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
Comment
Question by:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
17 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974310
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974317
Here it is all 3 Files.
Process.xlsm
Data.xlsx
Result.xlsx
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974322
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974324
in short 4 time sorting after - opening there two WB & Before closing these two WB.

Thanks
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974344
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974358
it is not sorting Sheets between Sheet "Start" - "Finish".

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974361
i.e sorting mean this

From this Before SortAfter Sort
Thanks
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974363
Oh, I though you wanted the data on sheets sorted!
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974367
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
 
LVL 27

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 39974373
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974386
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
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39974387
Thank You
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974393
Go ahead!
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974397
ok & now only 2 question left so pls be with me.

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974416
This is the One.

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974611
Any luck for new question?
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974662
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question