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]

Without seeing any information, one method could be to create a global boolean and set it to TRUE in the runall macro, in the individual macros you could edit them so that when called if the the global boolean is TRUE then it will append all of the data into one table, if it is FALSE then it will output to the intended sheet.

It is something like that or writing a master macro that will pull all of the data needed in one go.

 

[Excelexcel86]

Yes that is fine to do the and how do I put it into individual macros

 

[dmt32]

Yes that is fine to do the and how do I put it into individual macros

Sharing the code with forum would be helpful

Dave

 

[Excelexcel86]

Option explicit

Sun openlatestfile

Dim mypath as string
Dim myfile as string
Dim latestfile as string
Dim latestdate as date

The below path is an example
Mypath = “s:\jobs\area\people\”

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

workbooks.open mypath & latestfile

End sub

That’s my code the only bit that changes for every sheet is mypath =

 

[Excelexcel86]

Sorry these should be lmd

land = filedatetime (mypath & myfile)
If lad > latestdate then

any help be much appreciated in opening them all up into one sheet

 

[Excelexcel86]

Any ideas guys still struggling

 

[Georgiboy]

The problem is you are opening the sheets as seperate workbooks so it is not a case of just opening them all into one sheet as the current code just opens a file.

To get to where you want you may need to open each file and extract the data from each file to then append the data into one table so to speak, this will require us to understand the layout of the files being opened to know how to consolidate them best.

Are each of these files that are being opened in different file locations?

I ask as power query is good at pulling multiple files into one big table.

Below is an example of what i mentioned before although there are other ways around it like having a list of the file locations and names and using those to import all of the files:

Dim rAll As Boolean ''''''''''' global Boolean dim

Sub runall()
    rAll = True ''''''' set Boolean as true

    Call Sheet1.openlatestfile
    Call Sheet2.openlatestfile
    Call sheet3.openlatestfile
    'etc...
    
    rAll = False '''''''' set Boolean to false after multi import
End Sub


Sun openlatestfile
    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
    
    Set wsD = Sheets("Run All") '''''' Sheet to write values to
    mypath = "s:\jobs\area\people\"
    
    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
    
    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

 

[Excelexcel86]

Yes there are all in different file locations but on the same s drive path

 

[Excelexcel86]

How would I use file names and locations to get all