update in the name of the spreadsheet - macro will still pick up the data

[Jeffreyxx01]

Hello guys,

is it possible to have an automatic update in the code that will find the name of the spreadsheet as for example when I pull the data from different workbook finding the report as it is now, but assuming the report is called now: Report (1)

is it possible to have the macro taking the report even if the name is not specific to report but can have it like Report (x)
X would be for the number in the bracket,

Thanks for the help

Sub PullDataWB()
   
   Dim Wbk As Workbook
   Dim Sht As Worksheet
   Dim Usdrws As Long
   Dim LastRow As Long


Application.ScreenUpdating = False


Set Sht = ActiveWorkbook.Sheets("Pipeline Worker")
   ChDrive "W:"
   ChDir "W:\Insights Team\ALL ACADEMIC\Reporting\Weekly RAM\Pathways\RawData"
   Fname = Application.GetOpenFilename(FileFilter:="xls Files (*.xls*), *.xls*", Title:="Select a file", MultiSelect:=False)
   If Fname = "False" Then
      MsgBox "no file selected"
      Exit Sub
    Else
    Set Wbk = Workbooks.Open(Fname)
[COLOR=#ff0000]    With Wbk.Sheets("Report") >>>>> For example having ("Report (1)")[/COLOR]
    LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      Sht.Range("A2:AC" & LastRow).ClearContents
      .Range("A2:AC" & LastRow).Copy
      Sht.Range("A2").PasteSpecial xlPasteValues
   End With
Application.DisplayAlerts = False
   Wbk.Close , False
Application.DisplayAlerts = True
   Usdrws = Sht.Range("AC" & Rows.Count).End(xlUp).Row
   Sht.Range("AD2:CE" & Usdrws).FillDown
End If
      
End Sub

 

[mumps]

Try:

With Wbk.Sheets("Report*")

 

[Jeffreyxx01]

Hi

That does not work,

 

[mumps]

Try:

Dim ws As Worksheet
For Each ws In Sheets
    If ws.Name Like "Report*" Then
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Sht.Range("A2:AC" & LastRow).ClearContents
        ws.Range("A2:AC" & LastRow).Copy
        Sht.Range("A2").PasteSpecial xlPasteValues
    End If
Next ws

Keep in mind that the way you have written your code, LastRow refers to the "Report*" sheet and you are using that variable in both Sht and in the "Report*" sheet.

 

[Jeffreyxx01]

Thanks but I tried and I dont have it working unfortunately,

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your two files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.