Code path

[StellaArtois]

Hi all i have a code that searches data from a list of workbooks found in a folder

The data is copeied and pasted into a column i can utilise for reporting

this works well? But....

Is there a way to manipulate the filepath to look for workbooks within the main folder and any folders within the main folder

Sub Update_Backorders()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim rng As Range

   'myPath = "\\dta01\AftService\shops\Admin\Parts & Materials\Parts Lists WIP\Other\"
   myPath = "C:\Users\uknown\Desktop\WIP\Main\"

  myExtension = "*.xls"

  myFile = Dir(myPath & myExtension)


Set rng = ThisWorkbook.Sheets("Other").Range("C3")
x = 0

  Do While myFile <> ""
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
      
      wb.Worksheets(1).Range("ET16:FL512").Copy
      ThisWorkbook.Sheets("Other").Activate
      rng.Offset(x).PasteSpecial xlPasteValues
      Application.CutCopyMode = False
      Set Copiedrng = Selection
      
      For Each cell In Copiedrng.Columns(1).Cells
      yy = cell.Address
        If cell.Value = "" Then
          RowLength = cell.Row
          Exit For
        End If
      Next cell
    
      x = RowLength - rng.Row + 0
                   
      wb.Close SaveChanges:=False

      myFile = Dir
  Loop
  
    Range("A1").Select
    Sheets("Report").Select
    Range("A1").Select

    Call Calculation_Automatic

End Sub
[code/]

 

[Jeffrey Mahoney]

I really sounds like you need some very basic training on how to navigate VBA. You might want to find some videos on YouTube. This site has lots of training options

Below is a screenshot of the VBA project for one of my workbooks. The top portion lists Excel Objects with are Sheet modules and workbook modules. The bottom portion is made up of many standard modules. Standard modules are all the same regardless of the name. You can right click on "Modules" and choose to add a new module. When you do that, a new blank canvas opens to the right where you can write some new code or paste some code.

After you paste code, you can put your cursor inside one the SUBS and press F5 to run the macro. Adding all the code I have posted above will allow you to see how I was trying to help.

If you have questions beyond this, you will need to get more familiar with VBA before attempting to add or change code.

I assumed that because you had posted, what seems like rather mature code, on your first post, that you understood how to incorporate the code I gave you. This is a place to learn, so ask questions at the level you need.