Macro Question

[LLaDue02]

Hello all! I'm struggling with a macro that I'm trying to create that will pull data into a Master workbook from a different workbook that is updated weekly.

The problem I'm having is that the file I receive weekly is always named differently (updated for the week ending) and 7 of the 8 tabs in the workbook are also named differently (one for each day of the week that applies in that week ending range).

If it was a static file name, the macro is a piece of cake and works perfectly. I've read a great deal on a number of forums about how you can set it the macro to look at an ACTIVE workbook, rather than a specifically named one, but I just can't seem to get that to work.

Below is my macro for the specifically name file; what do I need to do differently so that I can run it on the file I receive each week just by having it open and active?

Sub SecData()
'
' SecData Macro
' Macro to move security badge-in data from weekly file to Master Security Log workbook.  Will overwrite Sheet1
'


'
    Sheets("Sheet1").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
    Range("A2").Select
    Windows("Framingham counts for the week ending 06-02-18.xlsx").Activate
    Sheets("05-27").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Copy
    Windows("Master Security Logs.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A13").Select
    Windows("Framingham counts for the week ending 06-02-18.xlsx").Activate
    Sheets("05-28").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Master Security Logs.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=18
    Range("A32").Select
    Windows("Framingham counts for the week ending 06-02-18.xlsx").Activate
    Sheets("05-29").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Master Security Logs.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D32").Select
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=18
    Range("A2154").Select
    Windows("Framingham counts for the week ending 06-02-18.xlsx").Activate
    Sheets("05-30").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Master Security Logs.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D2154").Select
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=9
    Range("A4378").Select
    Windows("Framingham counts for the week ending 06-02-18.xlsx").Activate
    Sheets("05-31").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Master Security Logs.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D4378").Select
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=12
    Range("A6638").Select
    Windows("Framingham counts for the week ending 06-02-18.xlsx").Activate
    Sheets("06-01").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Master Security Logs.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D6638").Select
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=18
    Range("A8435").Select
    Windows("Framingham counts for the week ending 06-02-18.xlsx").Activate
    Sheets("06-02").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Master Security Logs.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollRow = 7397
    ActiveWindow.ScrollRow = 2466
    ActiveWindow.ScrollRow = 1
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[PatOBrien198]

Try defining your workbooks up front, then refer to the workbook objects....

Dim wbMaster as Workbook
set wbMaster = activeworkbook

dim wbCounts as Workbook
set wbCounts = Workbooks(Windows(2).Parent.Name)

'depending on the order that you opened them
if wbMaster.Name = wbCounts.Name then
     set wbCounts = Workbooks(Windows(1).Parent.Name)
end if

Then, instead of:

Windows("Master Security Logs.xlsx").Activate
'......
Windows("Framingham counts for the week ending 06-02-18.xlsx").Activate

You could use:

wbMaster.Activate
'.......
wbCounts.Activate