Select data on worksheet when worksheet name is not constant

[RFLundgren]

Hi there, back again!

I have some code that allows a user to select a workbook to open. Once opened, I need to select all the data on the active tab and then copy it to another workbook.

The workbook being opened only contains a single tab, and if I rename the tab and reference it directly in the code, it of course works, however each time the original report is run, the tab has a different name, although this name does have some constant elements.

The code below allows me to select a workbook to open, and works as long as the sheet name matches. how do I use a wildcard to manage this scenario?

Public Sub GetFreshDeskData()

    'Open raw data file saved from Freshdesk to utilise in the monthly report

    Dim targetWorkbook As Workbook
    Set targetWorkbook = Application.ThisWorkbook
      
   
    'Dim Filter As String
    'Filter = "Text files (*.xlsx),*.xlsx"

    Dim Caption As String
    Caption = "Please Select the Freshdesk Monthly Tickets Report! "

    Dim Ret As Variant
    Ret = Application.GetOpenFilename '(Filter, , Caption)

    If VarType(Ret) = vbBoolean And Ret = False Then Exit Sub

    Dim wb As Workbook
    Set wb = Workbooks.Open(Ret)
    
    targetWorkbook.Worksheets("RawData").Cells.Clear
        
    wb.Worksheets("Tickets").UsedRange.Copy targetWorkbook.Worksheets("RawData").Range("A1")
        
    wb.Close SaveChanges:=False
    
End Sub

This is probably relatively easy to do, but it has been 20 years since I last coded, so lots to try and remember!

Many thanks!

Richard

 

[gordsky]

how about wb.sheets(1). Think that should reference the first worksheet of the open workbook so if you only have one sheet i think it might work.

 

[RFLundgren]

Hey Gordsky

Thanks for that, worked perfectly!

Richard

 

[gordsky]

Hey Gordsky

Thanks for that, worked perfectly!

Richard

glad i could help