Help req. in VBA for Excel

[shaharyar]

Dear all
any one help me please

in the following code i want to paste file name with data from different source file but its not properworking


Private Sub CommandButton1_Click()
Dim SrcBook As Workbook
Dim TrgtBook As Workbook
Dim fso As Object
Dim f As Object
Dim ff As Object
Dim i As Long
Dim SrcLCell
Dim TrgtLCell
Dim fname
Dim row As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set TrgtBook = ThisWorkbook
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("C:\FR\")
Set ff = CreateObject("Scripting.FileSystemObject")

For Each ff In f.Files
If ff.Name Like "*.xls" Then
Workbooks.Open Filename:=f & "\" & ff.Name
fname = ff.Name
Set SrcBook = ActiveWorkbook
For i = 1 To SrcBook.Sheets.Count
SrcBook.Sheets(i).UsedRange
'/ TrgtBook.ActiveSheet.UsedRange
TrgtBook.Sheets(2).UsedRange
SrcLCell = SrcBook.Sheets(i).Cells(SrcBook.Sheets(i).Cells.SpecialCells(xlLastCell).row, SrcBook.Sheets(i).Cells.SpecialCells(xlLastCell).Column).Address

If TrgtBook.Sheets(2).Cells.SpecialCells(xlLastCell).row > 1 Then

TrgtLCell = TrgtBook.Sheets(2).Cells(TrgtBook.Sheets(2).Cells.SpecialCells(xlLastCell).row + 1, 2).Address
Else
TrgtLCell = TrgtBook.Sheets(2).Cells(TrgtBook.Sheets(2).Cells.SpecialCells(xlLastCell).row, 2).Address
End If

SrcBook.Sheets(i).Range("A6:" & SrcLCell).Copy
TrgtBook.Sheets(2).Range(TrgtLCell).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Do Until TrgtBook.Sheets(2).Cells(row + 6) <> ""
TrgtBook.Sheets(2).Cells(row + 6, 1) = fname
row = row + 1
Loop
Next i

SrcBook.Saved = True
Workbooks(ff.Name).Close
End If
Next ff

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[SkipVought]

Hi,

but its not properworking

What does that mean? That tell's us nothing specific!

Do you get an error? If so, what error? On what statement?

Do you get an incorrect result? If so, what is the result? What result do you expect?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

I would strongly recommend you fully declare your variables. These are variants, and they are not used as such.

Dim SrcLCell
Dim TrgtLCell
Dim fname



Gerry