Seamus2626
asked on
If no, dont call sub within Sub
Hi,
I have attached a sub i have below that does some things and then calls these subs
Call DoMatches
Call SophisFiles
Call SophisFileHK
Call SophisFilesUS
I want an If statement to put at the beginning of this sub which says
Is Hong Kong Saved? if yes, call the sub as normal, if no, ignore that sub in code &
Is New York Saved? if yes, call the sub as normal, if no, ignore that sub in code
Is this possible?
Thanks
Seamus
I have attached a sub i have below that does some things and then calls these subs
Call DoMatches
Call SophisFiles
Call SophisFileHK
Call SophisFilesUS
I want an If statement to put at the beginning of this sub which says
Is Hong Kong Saved? if yes, call the sub as normal, if no, ignore that sub in code &
Is New York Saved? if yes, call the sub as normal, if no, ignore that sub in code
Is this possible?
Thanks
Seamus
Sub GroupupFiles()
Dim lngStartRow As Long
Dim lngLastrow As Long
Dim wksDest As Worksheet
Set wksDest = ThisWorkbook.Sheets("Match")
With wksDest
Set wksDest = Workbooks("tom.xls").Sheets("Match")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Call SortAspa
Sheets("Match").Select
Cells.Delete
lngStartRow = .Range("A" & .Rows.Count).End(xlUp).Row
ThisWorkbook.Sheets("ASPA").Range("E1:E1000").Copy .Range("A" & lngStartRow)
lngLastrow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("B1").Value = "System"
.Range("B2", .Cells(lngLastrow, "B")).FormulaR1C1 = "=if(RC[-1]<>"""",""ASPA"","""")"
.Range("C1").Value = "Book"
lngStartRow = .Range("C" & .Rows.Count).End(xlUp).Row
ThisWorkbook.Sheets("ASPA").Range("N1:N1000").Copy .Range("C" & lngStartRow)
ThisWorkbook.Sheets("ASPA").Range("AB1:AB1000").Copy .Range("D" & Rows.Count).End(xlUp)(1)
ThisWorkbook.Sheets("ASPA").Range("T1:T1000").Copy .Range("E" & Rows.Count).End(xlUp)(1)
ThisWorkbook.Sheets("ASPA").Range("L1:L1000").Copy .Range("I" & Rows.Count).End(xlUp)(1)
Sheets("Match").Select
lngLastrow = .Range("D" & .Rows.Count).End(xlUp).Select
Range(Selection, Selection.End(xlUp)(2)).Select
Range("A1").Select
lngLastrow = .Range("A" & .Rows.Count).End(xlUp).Row
lngStartRow = lngLastrow + 1
ThisWorkbook.Sheets("Phoenix").Range("B2:B1000").Copy .Range("A" & lngStartRow)
lngLastrow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range(.Cells(lngStartRow, "B"), .Cells(lngLastrow, "B")).FormulaR1C1 = "=if(RC[-1]<>"""",""Phoenix"","""")"
ThisWorkbook.Sheets("Phoenix").Range("P2:P1000").Copy .Range("D" & Rows.Count).End(xlUp)(2)
ThisWorkbook.Sheets("Phoenix").Range("I2:I1000").Copy .Range("C" & Rows.Count).End(xlUp)(2)
ThisWorkbook.Sheets("Phoenix").Range("C2:C1000").Copy .Range("I" & Rows.Count).End(xlUp)(2)
.Range(.Cells(lngStartRow, "E"), .Cells(lngLastrow, "E")).FormulaR1C1 = "=if(RC[-1]<0,""DR"",""CR"")"
Columns("E:E").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
lngLastrow = .Range("D" & .Rows.Count).End(xlUp).Select
Range(Selection, Selection.End(xlUp)(2)).Select
End With
For Each Cell In Selection.Cells
Cell.Value = Abs(Cell.Value)
Next Cell
Call DoMatches
Call SophisFiles
Call SophisFileHK
Call SophisFilesUS
Sheets("Match").Select
With ActiveSheet
R = .Range("A" & Rows.Count).End(xlUp).Row
Range("F1").Select
Selection = "Div"
Range("G1").Select
Selection = "Match1"
Range("H1").Select
Selection = "Match"
Range("C1").Select
Selection = "Book"
Range("H2").Select
Selection = "=IF(ISNUMBER(SEARCH(""No Match"", G2)),""No Match"",""Match"")"
.Range("H2:H2").Copy .Range("H2:H" & R)
End With
Call MoveNoMatches
Sheets("Match").Select
With Sheets("Match").Range("G3")
If Len(.Value) > 0 Then .Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
With Sheets("No Match").Range("D6")
If Len(.Value) > 0 Then .Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
Call EndFormat
Sheets("Match").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thats perfect Pratima!
Thank you very much
Seamus
Thank you very much
Seamus
this is for one
'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Is Hong Kong Saved")
If Answer = vbYes Then
Call DoMatches
End If