Get data from other open excel sheet without using filename

[mrmlee2209]

Hi All.

I'm doing daily gathering of data from multiple sources to a master spreadsheet.

Most of the daily data is emailed automatically each day.

For 3 out of 4 of the sheets I need, I've simply created a macro button. I open the excel attachment on the email, go to my master sheet, click the button and it selects the data from the attachment excel and paste-values into the master sheet. Simple.

However, for the fourth email attachment, the excel filename is always different, it appends the date to the file name. Hence, when I click the button I've created, it doesn't match the file name and faults.

Here's the script for it...

Sub Get_Stark_Data()
'
' Get_Stark_Data Macro
'

'
    Windows("Meter-Sequential_1-Day_CSV-kWh_Electricity_Ending-06-09-2021.csv"). _
        Activate
    Range("I10:BD10").Select
    Selection.Copy
    Windows("Export Monitoring Data.xlsm").Activate
    Range("C18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Meter-Sequential_1-Day_CSV-kWh_Electricity_Ending-06-09-2021.csv"). _
        Activate
    ActiveWindow.Close
End Sub

As you can see, the filename would be different for tomorrows data and the scrip would not work.

How can I amend the script to select the other open worksheet (only have 2 open at once) rather than using a specific filename? I was hoping replacing the date with wildcard* would work, but it didn't.

Many thanks for your help... manually copying and pasting this data was getting tedious. I'm 3/4 of the way there to help reduce that!

 

[johnnyL]

?

"Meter-Sequential_1-Day_CSV-kWh_Electricity_Ending-*.csv")

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Get_Stark_Data()
'
' Get_Stark_Data Macro
'
   Dim Wbk As Workbook
   For Each Wbk In Workbooks
      If Wbk.Name Like "Meter-Sequential_1-Day_CSV-kWh_Electricity_Ending*" Then Exit Sub
   Next Wbk
      
    Wbk.Sheets(1).Range("I10:BD10").Copy
    Workbooks("Export Monitoring Data.xlsm").Range("C18").PasteSpecial xlPasteValues
    Wbk.Close
End Sub

 

[mrmlee2209]

Hi & welcome to MrExcel.
How about

Sub Get_Stark_Data()
'
' Get_Stark_Data Macro
'
   Dim Wbk As Workbook
   For Each Wbk In Workbooks
      If Wbk.Name Like "Meter-Sequential_1-Day_CSV-kWh_Electricity_Ending*" Then Exit Sub
   Next Wbk
     
    Wbk.Sheets(1).Range("I10:BD10").Copy
    Workbooks("Export Monitoring Data.xlsm").Range("C18").PasteSpecial xlPasteValues
    Wbk.Close
End Sub

Thanks Fluff...

Didn't quite work but lead me in the right direction. Ended up with:

Sub Get_Stark_Data()
'
' Get_Stark_Data Macro
'

'
    Dim Wbk As Workbook
        
    For Each Wbk In Application.Workbooks
        If Wbk.Name Like "Meter-Sequential_1-Day_CSV-kWh_Electricity_Ending-*" Then
            Wbk.Activate
            ActiveSheet.Range("I10:BD10").Copy
            Workbooks("Export Monitoring Data.xlsm").Activate
            ActiveSheet.Range("C18:AX18").PasteSpecial xlPasteValues
            Wbk.Activate
            ActiveWindow.Close
        End If
    Next Wbk

End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback.