Exit Sub depending on no of sheets

[howard]

I have the following macro to open a source workbook and to copy from sheet1 to sheet 1 on the destination workbook

The maximum number of sheets on the source workbook is 3


However if there is only 1 sheet on the source workbook , then only macro Open_FPSTAT4 must be activated, if here are two sheets then Open_FPSTAT4 & Open_FPSTAT5 must be activated, If 3 sheets then Open_FPSTAT4, Open_FPSTAT5 & Open_FPSTAT6 must be activated


It would be appreciated if someone could amend my code regarding the above

 Sub Extract_Int()

Open_FPSTAT4
Open_FPSTAT5
Open_FPSTAT6

End Sub


Sub Open_FPSTAT4()
ChDir ("C:\extract")

Clear_Sheet1
Dim nb As Workbook, tw As Workbook, ts As Worksheet
A = Application.GetOpenFilename
If A = False Or IsEmpty(A) Then Exit Sub
With Application
    .ScreenUpdating = False
    End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(A)

nb.Sheets(1).UsedRange.Copy Destination:=ThisWorkbook.Sheets(1).Range("A1")  


 ChDir ("C:\my documents")

 End Sub 


Sub Open_FPSTAT5()
ChDir ("C:\extract")

Dim nb As Workbook, tw As Workbook, ts As Worksheet
A = Application.GetOpenFilename
If A = False Or IsEmpty(A) Then Exit Sub
With Application
    .ScreenUpdating = False
    End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(A)
If nb.Worksheets.Count < 2 Then
Exit Sub
Else
nb.Sheets(2).UsedRange.Copy Destination:=ThisWorkbook.Sheets(2).Range("A1")  'change to suit ones needs

End If

 ChDir ("C:\my documents")
 
 End Sub

Sub Open_FPSTAT6()
ChDir ("C:\extract")

Dim nb As Workbook, tw As Workbook, ts As Worksheet
A = Application.GetOpenFilename
If A = False Or IsEmpty(A) Then Exit Sub
With Application
    .ScreenUpdating = False
    End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(A)


nb.Sheets(3).UsedRange.Copy Destination:=ThisWorkbook.Sheets(3).Range("A1") 

End If


 ChDir ("C:\my documents")
 
 End Sub

 

[Fluff]

Rather than calling all the macros from Extract_Int, only call the first & then add this to the end of that macro (ie FPSTAT4)

If Sheets.Count >= 2 Then Open_FPSTAT5

With the same sort of thing at the end of FPSTAT5

 

[howard]

Thanks for the code. It works perfectly

 

[Fluff]

Glad to help & thanks for the feedback