Autofill Filename to end of row

[bennyamy]

I have a script that extracts the filename from a file stored on a SharePoint site. It all works properly but I need the script to autofill the file name down to the last row. What am I missing?
The last line in this code is when the filename gets populated on the first cell in destination file, I need it to fill down all rows in the column to last row

[CODE]Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim imgTitle As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
Dim FolderName As String
Dim LR As Long
Dim x As Long
Dim n As Integer
Dim temp As Long
Dim rng As Range, ar As Range
Dim nr As Range
Dim mr As Range
Dim LoginName As String
Dim WinShuttle As Application
Dim Connection As Object
Dim session As Object
Dim SAPApp As Object
Dim SAPCon As Object, SAPSesi As Object
Dim SAPGUIAuto As Object
Dim sapConnection As Object
Dim d As String, ext, y
Dim srcPath As String, destPath As String, srcFile As String
Application.ScreenUpdating = False



LoginName = UCase(GetUserID)

'Designates Sheet for pasting from multiple workbooks
Set SummarySheet = Worksheets(3)


'Modify folder path when transferring between process owners
FolderPath = "Z:\"

'Nrow keeps track of where to insert new rows in the workbook
NRow = 1
LR = ActiveSheet.Range("U" & Rows.Count).End(xlUp).Row
'Call directory the first time pointing it to all excel files
FileName = Dir(FolderPath & "*.xls*")
'Loop until directory returns empty string
Do While FileName <> ""
'Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)

'Set the cell in column U to house the file name
SummarySheet.Range("U:U" & NRow).Value = FileName


'Set the range to be A1000 through W1000
On Error Resume Next
Set SourceRange = WorkBk.Worksheets(1).Range("A2:M300")
On Error GoTo 0
'Set the destination range
Set DestRange = SummarySheet.Range("A" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count)

'Copy over values from the source to the destination
DestRange.Value = SourceRange.Value
SummarySheet.Range("U").AutoFill Destination:=Range("U1:U" & LR)[/CODE]

[bennyamy]

Wrong line in previous post, this is the correct line

Please Login or Register  to view this content.

[davesexcel]

Would it not be

Please Login or Register  to view this content.

even this may have to be:

Please Login or Register  to view this content.

[bennyamy]

I tries that & got a Method 'Range' of object'_Global failed error

[davesexcel]

I tries that & got a Method 'Range' of object'_Global failed error

Oh, what did you try?

[bennyamy]

I tried what you suggested before originally posting this.

[davesexcel]

I find your lack of detail disturbing....

[bennyamy]

I tried what is shown below & received this error: Method 'Range' of object'_Global failed error


Formula:

Please Login or Register  to view this content.

even this may have to be:


Formula:

Please Login or Register  to view this content.

[bennyamy]

I also tried the below which copies the file name down but it copies over each file name

Please Login or Register  to view this content.