Cant Set FirstSel = ws.select. SecondSel = ws.select

[Vanda_a]

Dear all

Could someone help me with this thing please

Dim FirstSel As .....
Dim SecondSel As ....
Dim ws As Worksheets

    For Each ws In Worksheets
        Select Case True
            Case ws.Name Like "INV*"
                FirstSel = ws.Select '''''''''''''''''''''''''''''Debug here.
            Case ws.Name Like "Reim*"
                SecondSel = ws.Select '''''''''''''''''''''''''''''Debug here.
            Case ws.Name Like "DN*"
                MsgBox ws.Name
            Case Else
                MsgBox ws.Name
        End Select
    Next ws

Why can't I do the thing like that?

 

[RoryA]

Select doesn't return an object - it just selects something. If you are trying to assign a variable, use:

Set FirstSel = ws

 

[Vanda_a]

Select doesn't return an object - it just selects something. If you are trying to assign a variable, use:

Set FirstSel = ws

What to dim FirstSel and Second Sel? Dim as Variant?

And how can I select both of them?

my next code

If FirstSel Like "INV*" And SecondSel Like "Reim*" Then
Selection.SaveAs Filename:=Path & FirstSel.Name & ". xlsx         "''''''''''''''''Select both of them and save as
End if

Thank you very much

 

[Vanda_a]

If FirstSel Like "INV*" And SecondSel Like "Reim*" Then ''''''''''''''also Debug

hmmmm (headbang)

 

[RoryA]

ADVERTISEMENT

It appears you want String variables, in which case you'll need:

Dim FirstSel As String
Dim SecondSel As String
Dim ws As Worksheet

    For Each ws In Worksheets
        Select Case True
            Case ws.Name Like "INV*"
                FirstSel = ws.Name
            Case ws.Name Like "Reim*"
                SecondSel = ws.Name
            Case ws.Name Like "DN*"
                MsgBox ws.Name
            Case Else
                MsgBox ws.Name
        End Select
    Next ws

 

[Vanda_a]

It appears you want String variables, in which case you'll need:

Dim FirstSel As String
Dim SecondSel As String
Dim ws As Worksheet

    For Each ws In Worksheets
        Select Case True
            Case ws.Name Like "INV*"
                FirstSel = ws.Name
            Case ws.Name Like "Reim*"
                SecondSel = ws.Name
            Case ws.Name Like "DN*"
                MsgBox ws.Name
            Case Else
                MsgBox ws.Name
        End Select
    Next ws

Yeah. Thanks so much.

    If FirstSel Like "INV*" And SecondSel Like "Reim*" Then ''''''''''''''''''''''''''''If FirstSel = "INV*" and SecondSel = "Reim*" Then
        Worksheets(FirstSel).Select
        Worksheets(SecondSel).Select
        Selection.SaveAs Filename:=Path & FirstSel & ". xlsx" ''''''''''''''''''''''Debug here
    End If

Please kindly help to adjust

 

[RoryA]

ADVERTISEMENT

Try:

    If FirstSel Like "INV*" And SecondSel Like "Reim*" Then ''''''''''''''''''''''''''''If FirstSel = "INV*" and SecondSel = "Reim*" Then
        Worksheets(FirstSel).Select
        Worksheets(SecondSel).Select False
        Activewindow.Selectedsheets.COpy
activeworkbook.SaveAs Filename:=Path & FirstSel & ". xlsx" ''''''''''''''''''''''Debug here
    End If

 

[Vanda_a]

Try:

    If FirstSel Like "INV*" And SecondSel Like "Reim*" Then ''''''''''''''''''''''''''''If FirstSel = "INV*" and SecondSel = "Reim*" Then
        Worksheets(FirstSel).Select
        Worksheets(SecondSel).Select False
        Activewindow.Selectedsheets.Copy
activeworkbook.SaveAs Filename:=Path & FirstSel & ". xlsx" ''''''''''''''''''''''Debug here
    End If

Yeah. It works so well. Thank you very much

May I have some explanation please?

Worksheets(SecondSel).Select False '''''''''''''False means none select?
Activewindow.Selectedsheets.Copy ''''''''''''''''Ís it necessary?
Activeworkbook.SaveAs Filename:=Path & FirstSel & ".xlsx" '''''''''Can use ActiveSheets as a substitute?

 

[Vanda_a]

One more please

If FirstSel Like "INV*" And SecondSel Like "Reim*" Then  is the same as below one 
If SecondSel Like "Reim*" And FirstSel Like "INV*" Then

How to make it not the same?
Ex: Sheet1(INV123) and Sheet2(Reim123). The code executes ''''''''''''Í would like the code to execute only this part
Sheet1(Reim123) and Sheet2(INV123). The code still executes.... @_@