Calling Sub Routine from one tab, having data show on another

[schafers]

I'm trying to run this routine from a main tab (menu if you will), and allow another tab to update the actual values. The button that calls this is on the main menu, yet when I click it, the values stay on the menu tab, they don't go to the Worksheet labeled staging file infromation. They keep going to the tab the button was ran from.

Any ideas? Guessing I'm not using the right worksheets command?

Thanks Experts!
Steven

Private Sub CommandButton1_Click()

'The macro examples below assumes that your VBA project has added a reference to the Microsoft Scripting Runtime library.
'You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft Scripting Runtime.

' add headers
' Modify the folder name below - This is the only place you modify the code for the folder location
FlFolderName = Worksheets("Staging File Information").Range("N1")
With Worksheets("Staging File Information").Range("A3")
.Formula = "Folder contents:" & FlFolderName
.Font.Bold = True
.Font.Size = 12
End With
Worksheets("Staging File Information").Range("A4").Formula = "Windows File Name:"
Worksheets("Staging File Information").Range("b4").Formula = "Date Last Modified:"
Worksheets("Staging File Information").Range("A4:H4").Font.Bold = True
ListFilesInFolder (FlFolderName), True
' list all files included subfolders
Worksheets("Staging File Information").Calculate
Worksheets("MENU").Calculate
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
r = Worksheets("Staging File Information").Range("A4").Row + 1
For Each FileItem In SourceFolder.Files
' display file properties
Cells(r, 1).Formula = FileItem.Name
Cells(r, 2).Formula = FileItem.DateLastModified
r = r + 1 ' next row number
Next FileItem
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.Path, True
Next SubFolder
End If
Worksheets("Staging File Information").Columns("A:H").AutoFit
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
End Sub

 

[syntaxed]

It might work if you change:

Cells(r, 1).Formula = FileItem.Name
Cells(r, 2).Formula = FileItem.DateLastModified

to:

Worksheets("Staging File Information").Cells(r, 1).Formula = FileItem.Name
Worksheets("Staging File Information").Cells(r, 2).Formula = FileItem.DateLastModified