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

 

[Excelexcel86]

Hi I’ve managed to get part of it working but it is saying it’s only getting a couple from my file path then comes up with an error saying can’t find data even tho it is in the correct path I gave

 

[Excelexcel86]

H

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'

Hi it doesn’t go through all my file paths it stops on certain ones but if I put that file it stops on in my files sheet on its own it opens fine

 

[Excelexcel86]

H

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'

Hi it doesn’t go through all my file paths it stops on certain ones but if I put that file it stops on in my files sheet on its own it opens fine

H

Hi it doesn’t go through all my file paths it stops on certain ones but if I put that file it stops on in my files sheet on its own it opens fine

it highlights set wb = workbooks.open (mypath & latestfile)

 

[Georgiboy]

It was untested pseudo code, i have tested it today and made it run with files at my end. Give the below a test:

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
    Dim land As Date
   
    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 = Format(FileDateTime(mypath & myfile), "dd/mm/yyyy hh:mm:ss")
            If land > latestdate Then
                latestfile = myfile
                latestdate = Format(land, "dd/mm/yyyy hh:mm:ss")
            End If
            myfile = Dir
        Loop
        latestdate = 0
        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]

That’s great thanks

 

[Excelexcel86]

If I make changes to these sheets it don’t save over the original does it

 

[Georgiboy]

If you change the data in the 'Run All' tab it will not change the files in the folders. We opened each one in the code and extracted data so the 'Run All' tab is just data.

 

[Georgiboy]

That’s great thanks

You are welcome

 

[Excelexcel86]

Ok great thanks for ur help

 

[Excelexcel86]

Hi is there a way to modify this code and use it to get the most recent from different files in a share point folder ?