Open all sheets in current sheet

[Excelexcel86]

Hi guys my issue is I have created a macro on every sheet in a workbook to get most recent csv data from a folder I have then assigned this to a button that when clicked opens up into another sheet .this is fine if the person only wants to look at that specific data. The issue I have got is in my workbook I have created a master sheet where I have inserted a module that contains the following

Sub runall ()
Call sheet1.openlatestfile
Call sheet2.openlatestfile
Call sheet3.openlatestfile

And so on till sheet 62 but this opens all sheets separately I would like it to open in into one sheet any help with code for this please

 

[Georgiboy]

You would have a list in a sheet with all of the file paths

You basically loop over the values (file paths) and open each file, extract the information and paste it in a sheet in the workbook that hosts the code. You would then close the workbook that had the data extracted and the loop would move to the next file in the list. By doing this you would be using similar code as you were before but the values of 'mypath' would be from the table of file paths you created.

Untested code below for to show you what i mean:

Sub openalllatestfiles
    Dim mypath As String
    Dim myfile As String
    Dim latestfile As String
    Dim latestdate As Date
    Dim wb As Workbook, ws As Worksheet '''''' new dims
    Dim wsD As Worksheet ''''''''''' new dims
    Dim rCell As Range, wsFiles As Worksheet
   
    Set wsD = Sheets("Run All") '''''' Sheet to write values to (***Create this sheet***)
    Set wsFiles = Sheets("Files") '''''' list of paths (column A) (***Create this sheet***)
   
    For Each rCell In wsFiles.Range("A2:A" & wsFiles.Range("A" & Rows.Count).End(xlUp).Row).Cells
        mypath = rCell.Value
        If Right(mypath, 1) <> " \ " Then mypath = mypath & " \ "
        myfile = Dir(mypath & " * .csv", vbNormal)
        If Len(myfile) = 0 Then
            Exit Sub
        End If
        Do While Len(myfile) > 0
            land = FileDateTime(mypath & myfile)
            If lad > latestdate Then
                latestfile = myfile
                latestdate = lmd
            End If
           
            myfile = Dir
        Loop
       
        Set wb = Workbooks.Open(mypath & latestfile) ' capture opened workbook
        Set ws = wb.Sheets(1) ' capture sheet1 of opened workbook
        ws.UsedRange.Offset(1).Copy ' copy the range from the opened workbook
        wsD.Range("A" & wsD.Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues ' paste data into Run All sheet
        Application.CutCopyMode = False
        wb.Close False ' close the opned workbook without save
    Next rCell
End Sub

 

[Excelexcel86]

Where do I put the dim rall as Boolean part in my excel does that for with sheet 1 or in the run all code

 

[Georgiboy]

You can see that in the post i made thet had the rAll boolean in it, it goes above any of the Sub's in the main Module, the post above is another method where it will import all from the list without the use of a boolean.

 

[Excelexcel86]

Does this how I’m my sheets I have or the module with run all as I’m getting script out of range

If rAll = True Then ' if boolean we set is true
Set wb = Workbooks.Open(mypath & latestfile) ' capture opened workbook
Set ws = wb.Sheets(1) ' capture sheet1 of opened workbook
ws.UsedRange.Offset(1).Copy ' copy the range from the opened workbook
wsD.Range("A" & wsD.Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues ' paste data into Run All sheet
wb.Close False ' close the opned workbook without save
Else
Workbooks.Open mypath & latestfile ' if boolean is false
End If
End Sub

 

[Georgiboy]

Does this how I’m my sheets I have or the module with run all as I’m getting script out of range

Sorry i don't understand the above

I have mentioned two methods here, one in post 11 and another method in post 8

Personally i think the method in post 11 is the way to go

 

[Excelexcel86]

Hi so where do I put all my file paths it option 11

 

[Excelexcel86]

And also where it says list of paths (column a ) what is the column a there for

 

[Georgiboy]

Hi so where do I put all my file paths it option 11

And also where it says list of paths (column a ) what is the column a there for

So in the below piece of code and annotation:

Set wsD = Sheets("Run All") '''''' Sheet to write values to (***Create this sheet***)
Set wsFiles = Sheets("Files") '''''' list of paths (column A) (***Create this sheet***)

You need to create a sheet called 'Run All', this sheet will hold the data from all of the seperate files
You also need to create a sheet called 'Files' and list your file paths in column A starting at row 2, the (Column A) part was to let you know where to place the file paths in the sheet named 'Files'

 

[Excelexcel86]

Ok does the code for run all go in the sheet run all or a module ?

 

[Excelexcel86]

Also the next rcell isn’t working at the bottom is keeps saying compile error : next without for