Help with macro

[Guest]

The following macro puts the file names on the worksheet that has the command
button. How can I put the file names on a different worksheet?




Private Sub CommandButton1_Click()

Dim FN As String ' For File Name
Dim ThisRow As Long
Dim FileLocation As String

Application.ScreenUpdating = False

FileLocation = "C:\Documents and Settings\nc1\Desktop\TAS forms\*.xls"
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop

End Sub

 

[Dave Peterson]

One way:

Option Explicit
Private Sub CommandButton1_Click()

Dim FN As String ' For File Name
Dim ThisRow As Long
Dim FileLocation As String
Dim newWks As Worksheet

Set newWks = Worksheets.Add
Application.ScreenUpdating = False

FileLocation = "C:\Documents and Settings\nc1\Desktop\TAS forms\*.xls"
FileLocation = "C:\my documents\excel\*.xls"
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
newWks.Cells(ThisRow, 1) = FN
FN = Dir
Loop

End Sub