Hi,

My below code has a compile error : Expected: list seperator or )
Pls help solve it.


Sub Consolidation()


'Copy to the next line in the target file'

Dim i As Integer, wb As Workbook
Dim first, last As Long

'Open all files in the folder'
With Application.FileSearch
.NewSearch
.LookIn = "N:\Tax\FILE1"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count

'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

'Identify 1st row & last row of the target area'
first = ActiveSheet.Cells(Rows.Count, "C").End(xlDown).Row
first = first - 1
last = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("A" & first&":H" & last).Copy ======
error in here

'On to the next workbook
Next i
End With

End Sub

Put a space between first and &.

Range("A" & first &":H" & last).Copy

Mike F
"diepvic" wrote in message
...
Hi,

My below code has a compile error : Expected: list seperator or )
Pls help solve it.


Sub Consolidation()


'Copy to the next line in the target file'

Dim i As Integer, wb As Workbook
Dim first, last As Long

'Open all files in the folder'
With Application.FileSearch
.NewSearch
.LookIn = "N:\Tax\FILE1"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count

'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

'Identify 1st row & last row of the target area'
first = ActiveSheet.Cells(Rows.Count, "C").End(xlDown).Row
first = first - 1
last = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("A" & first&":H" & last).Copy
======
error in here

'On to the next workbook
Next i
End With

End Sub

Also Dim first as Long.

Dim first, last As Long
Should be
Dim first As Long, last As Long

Otherwise first is Dimmed as Variant and only last is Dimmed as Long.

Also, any chance that first could = 1?
Then first = first - 1 would be in error.

Mike F

"Mike Fogleman" wrote in message
...
Put a space between first and &.

Range("A" & first &":H" & last).Copy

Mike F
"diepvic" wrote in message
...
Hi,

My below code has a compile error : Expected: list seperator or )
Pls help solve it.


Sub Consolidation()


'Copy to the next line in the target file'

Dim i As Integer, wb As Workbook
Dim first, last As Long

'Open all files in the folder'
With Application.FileSearch
.NewSearch
.LookIn = "N:\Tax\FILE1"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count

'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

'Identify 1st row & last row of the target area'
first = ActiveSheet.Cells(Rows.Count, "C").End(xlDown).Row
first = first - 1
last = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("A" & first&":H" & last).Copy ======
error in here

'On to the next workbook
Next i
End With

End Sub

That's brilliant

Thx so much

One more question, after I open each workbook, I want to close it without
saving and no prompt message asking "Do u want to save ..?" . Also each time
when I open the workbook, I do not want to update the workbook so can you
advise me the code to avoid pop-up message asking "Do you want to update the
workbook...?"

Appreciate your help.

"Mike Fogleman" wrote:

Also Dim first as Long.

Dim first, last As Long
Should be
Dim first As Long, last As Long

Otherwise first is Dimmed as Variant and only last is Dimmed as Long.

Also, any chance that first could = 1?
Then first = first - 1 would be in error.

Mike F

"Mike Fogleman" wrote in message
...
Put a space between first and &.

Range("A" & first &":H" & last).Copy

Mike F
"diepvic" wrote in message
...
Hi,

My below code has a compile error : Expected: list seperator or )
Pls help solve it.


Sub Consolidation()


'Copy to the next line in the target file'

Dim i As Integer, wb As Workbook
Dim first, last As Long

'Open all files in the folder'
With Application.FileSearch
.NewSearch
.LookIn = "N:\Tax\FILE1"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count

'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

'Identify 1st row & last row of the target area'
first = ActiveSheet.Cells(Rows.Count, "C").End(xlDown).Row
first = first - 1
last = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("A" & first&":H" & last).Copy ======
error in here

'On to the next workbook
Next i
End With

End Sub






.

Simple enough, although I didn't see any Close code,

Set wb = Workbooks.Open(Filename:=.FoundFiles(i), UpdateLinks:=0)
wb.Close savechanges:=False

Mike F
"diepvic" wrote in message
...
That's brilliant

Thx so much

One more question, after I open each workbook, I want to close it without
saving and no prompt message asking "Do u want to save ..?" . Also each
time
when I open the workbook, I do not want to update the workbook so can you
advise me the code to avoid pop-up message asking "Do you want to update
the
workbook...?"

Appreciate your help.

"Mike Fogleman" wrote:

Also Dim first as Long.

Dim first, last As Long
Should be
Dim first As Long, last As Long

Otherwise first is Dimmed as Variant and only last is Dimmed as Long.

Also, any chance that first could = 1?
Then first = first - 1 would be in error.

Mike F

"Mike Fogleman" wrote in message
...
Put a space between first and &.

Range("A" & first &":H" & last).Copy

Mike F
"diepvic" wrote in message
...
Hi,

My below code has a compile error : Expected: list seperator or )
Pls help solve it.


Sub Consolidation()


'Copy to the next line in the target file'

Dim i As Integer, wb As Workbook
Dim first, last As Long

'Open all files in the folder'
With Application.FileSearch
.NewSearch
.LookIn = "N:\Tax\FILE1"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count

'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

'Identify 1st row & last row of the target area'
first = ActiveSheet.Cells(Rows.Count, "C").End(xlDown).Row
first = first - 1
last = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Range("A" & first&":H" & last).Copy ======
error in here

'On to the next workbook
Next i
End With

End Sub






.