Quality report help

[Pheonix2332]

Hi all, I have previously made a report with a macro based on a single sheet copied from another workbook and created a random sample of cases managers to review. It has worked great but due to a shift in management there has been a slight change in how this needs to be done- instead of daily now done weekly for the past week so my previous code no longer works. Great from my piers point of view as less checks done on their work (potentially) but a headache for me - they want the data to be pulled from multiple sheets in the same workbook that vary by name each week as based on the days date determined from a range in the book the sheets are copied to, and want the report to generate 2 to 3 random cases for each staff ID in the sheet. To add additional difficulty each month the workbook is changed to a new book so will need to take this into account too. Is anybody able to help ? I have code to open the different work book but can’t figure out the code to use the range for the sheets names. I can add the code what I have thus far when in office tomorrow

 

[Micron]

can’t figure out the code to use the range for the sheets names.

This refers to range or ranges (cells or groups of cells) on sheets? Or do you mean the 'group' of sheets in a wb? If the latter, those sheets belong to a collection object that you can loop over.

 

[Pheonix2332]

This refers to range or ranges (cells or groups of cells) on sheets? Or do you mean the 'group' of sheets in a wb? If the latter, those sheets belong to a collection object that you can loop over.

sorry this was very vague last night when typing up, the document the Macro is based on has a sheet for input information for what needs to be collated - the user inputs the date from the recent Monday and a =working day formula is set to use the date in question and has the start sheet name to be selected and the end sheet to be selected - this is concatonated to include the phrase "Do Not Use" so the sheet names will be as an example for last week "11.04.22 Do Not use" for the first sheet and needs to copy the others within the range so 12.04,13.04,14.04 & 15.04. and then stop, the code I have used in the past was this

sub Copy()

Workbooks("april bfs.xlsx").worksheets("Export").range("A2:J").copy _
    workbooks("reports.xlsm").worksheets("Data").range("A2")
    
End Sub

but now due to the request to have a bigger sample of data to be used - 5 different sheets each week am stuck i need the first part of the code to read the referenced cells in the reports sheet to then k now what sheets it needs to copy

 

[Micron]

Sorry, still not following. Lots of business related info there that I can't relate to. You say the code is supposed to concatenate? I cannot see where that takes place in what you posted. If you can just explain it in simple terms as to what you have and what you need to have happen, that might clear things up a bit. F'rinstance
"I want to copy all sheets from wb1 into wb2 but in wb2 I want the sheets to have new names. The new names should have "Do Not Use" appended to the end of their existing name." or something like that. If there's any data that needs to be accessed (e.g. from a cell) to fulfill the goal, make sure you include that.
HTH

 

[Pheonix2332]

I have been able to get some further progress on this today and have the code below

Sorry, still not following. Lots of business related info there that I can't relate to. You say the code is supposed to concatenate? I cannot see where that takes place in what you posted. If you can just explain it in simple terms as to what you have and what you need to have happen, that might clear things up a bit. F'rinstance
"I want to copy all sheets from wb1 into wb2 but in wb2 I want the sheets to have new names. The new names should have "Do Not Use" appended to the end of their existing name." or something like that. If there's any data that needs to be accessed (e.g. from a cell) to fulfill the goal, make sure you include that.
HTH

I have been able to get this to work so far

Sub Report()

    Dim fd As FileDialog
    Dim Filechosen As Boolean
    Dim Savebutton As Boolean
    Dim sh As Worksheet
    Dim book As Workbook
    Dim lo As ListObject
    Dim iCol As Long
    Dim p As Integer, q As Integer
    
    
    Set fd = Application.FileDialog(msoFileDialogOpen)
    
    fd.Filters.Clear
    fd.Filters.Add "Old Excel Files", "*.xls"
    fd.Filters.Add "New Excel Files", "*.xlsx"
    fd.Filters.Add "macro Excel Files", "*.xlsm"
    fd.Filters.Add "any Excel Files", "*.xl*"
    
    fd.FilterIndex = 4
    
    fd.AllowMultiSelect = False
    
    fd.InitialFileName = "sharepoint location"
        
    Filechosen = fd.Show
    

    If Not Filechosen Then
        MsgBox " No File Selected"
        Exit Sub
    End If
        
    fd.Execute
      
For Each sh In Worksheets
    
                sh.Visible = True
    
Next sh

p = Worksheets.Count
    For q = 1 To p
    On Error Resume Next
With Worksheets(q).Range("A1").AutoFilter(10, "<>")
End With
Next q
'next step to copy sheets based on rang in worksheet(Sampledata)'


End Sub

what im needing now is for the code to see what file names are in the list in A1 on the sample data sheet in the workbook named "quality" and copy the data on each sheet from A2 to end and have each sheet data to be compiled into a list