Replies

Try this ARRAY entered formla if all grades are in CAPS

=IF(COUNTA(B3:G3)<6,"more grades",CHAR(INT(SUM(IF(B3:G3″”,CODE(B3:G3))/COUNTA(B3:G3)))))

do you want to copy to the sub sheet (located where????? sub folder…… Not at all clear here)

=w15/if(u15=””,800,900)*100

I am sick and can’t work on this but here is the basic idea then use autofilter

Month adavisor Military Member Military Spouse Civilian or Dependent Total

If we are to assume that all files are xl or the user know what excel file he wants, the filters are not necessary and my 2 liner works just fine.

Sub SelectFileGetAllSheetsSAS1()
Workbooks.Open Filename:=Application.GetOpenFilename(Title:=”Select File”)
Sheets.Copy after:=Workbooks(ThisWorkbook.Name).Sheets(“Master File”)
End Sub

My suggestion is to have ONE file with 5 tabs with all dates on one tab where you can filter for the months. Then just use sumproduct on the master sheet.
Or, ONE file with ONE tab and all data and use filter with SUBTOTAL at the top for the filtered data

Attach your file(s) and I’ll show you.

Zeddy, Thanks for the kind words. And, unless I forget (can be forgiven at my age), I always use dims. My last post doesn’t call for one. Option explicit at the top of my module (which I normally include in my posts). As you can tell, I generally try to use the “keep it simple for the AGGIES” (i am a University of Texas grad where we had a rival called Texas A & M that we ridiculed for being stupid) ie: How many aggies to change a light bulb, etc

You want simple. I’ll give you simple….

Sub SelectFileGetAllSheetsSAS1()
Workbooks.Open Filename:= _
Application.GetOpenFilename(Title:=”Select File”)
Sheets.Copy after:=Workbooks(ThisWorkbook.Name).Sheets(“Master File”)
End Sub

Us old folks ( I had my 79th birthday on income tax day) learn something new every day.

Based on the original post, why not simply

Sub SelectFileGetAllSheetsSAS()
Dim ds As String
Dim fNameAndPath As Variant
ds = ThisWorkbook.Name
fNameAndPath = Application.GetOpenFilename(FileFilter:= _
“Excel Files (*.XLS), *.XLS”, Title:=”Select File To Open”)
If fNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fNameAndPath
Sheets.Copy after:=Workbooks(ds).Sheets(“Master File”)
End Sub

40417-rz-mouse-position-v2 You may like this as well or better. See Attached

Code:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = 1 Then
 With Target.EntireRow
  .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
  .FormatConditions(1).Interior.ColorIndex = 35
 End With
End If
End Sub

You may also like this CHOOSE formula for col A
‘=CHOOSE(WEEKDAY(C3),”error”,”error”,”mm”,”Pb”,”error”,”mm”,”pb”)
and chg the CF to reflect =<today()

There is always less confusion when you attach a file with before/after examples along with the logic you provided.

Why dummy sheets??

=SUM(WK1:WK13,D4)

Here is part of a double_click event to goto yahoo finance for the symbol clicked.

ActiveWorkbook.FollowHyperlink Address:= _
“http://finance.yahoo.com/q?s=&#8221; & Target.Value