Hi All,

I need help with data consolidation. I have hardcoded file for data
consolidations as follows:

Range("B6").Select
Selection.Consolidate Sources:=Array( _
"'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
- Watchlist 1-45 Workbook Dec 07 Wk1.xls]Summary'!R6C2:R17C7" _
, _
"'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
- Watchlist 1-45 Workbook Dec 07 Wk2.xls]Summary'!R6C2:R17C7" _
, _
"'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
- Watchlist 1-45 Workbook Dec 07 Wk3.xls]Summary'!R6C2:R17C7" _
, _
"'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
- Watchlist 1-45 Workbook Dec 07 Wk4.xls]Summary'!R6C2:R17C7" _
), Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:= _
False
Range("B19").Select

It works. BUt when I change that to variables using this code:

Sub Consolidate_Cured()
'
Dim strFilePath As String
Range("B6").Select
strFilePath = ActiveWorkbook.Path

Selection.Consolidate Sources:=Array( _
" ' "& strFilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
Wk1.xls]Summary'!R6C2:R17C7" _
, _
" ' "& strFilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
Wk2.xls]Summary'!R6C2:R17C7" _
, _
" ' "& strfilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
Wk3.xls]Summary'!R6C2:R17C7" _
' _
" ' "& strFilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
Wk4.xls]Summary'!R6C2:R17C7" _
), Function:=xlSum, TopRow:=False, LeftColumn:=false, CreateLinks:= _
False

Range("B19").Select
End Sub

and VB return error said :compile error expected: List separator or ).
Can Anybody help me with the syntax please?

Thank You very3x much...
Jeff

Maybe something like this - what word wrap in the email:

Selection.Consolidate Sources:=Array( _
"'" & strFilePath & _
"\[MMT - Watchlist 1-45 Workbook Dec 07 Wk1.xls]Summary'!R6C2:R17C7", _
"'" & strFilePath & _
"\[MMT - Watchlist 1-45 Workbook Dec 07 Wk2.xls]Summary'!R6C2:R17C7", _
" ' " & strFilePath & _
"\[MMT - Watchlist 1-45 Workbook Dec 07 Wk3.xls]Summary'!R6C2:R17C7", _
"'" & strFilePath & _
"\[MMT - Watchlist 1-45 Workbook Dec 07 Wk4.xls]Summary'!R6C2:R17C7"), _
Function:=xlSum, _
TopRow:=False, _
LeftColumn:=False, _
CreateLinks:=False

--
Regards,
Tom Ogilvy


"Jholerjo" wrote:

> Hi All,
>
> I need help with data consolidation. I have hardcoded file for data
> consolidations as follows:
>
> Range("B6").Select
> Selection.Consolidate Sources:=Array( _
> "'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
> - Watchlist 1-45 Workbook Dec 07 Wk1.xls]Summary'!R6C2:R17C7" _
> , _
> "'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
> - Watchlist 1-45 Workbook Dec 07 Wk2.xls]Summary'!R6C2:R17C7" _
> , _
> "'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
> - Watchlist 1-45 Workbook Dec 07 Wk3.xls]Summary'!R6C2:R17C7" _
> , _
> "'G:\Loss Mitigation\Unsecured Reporting\MMT\Watchlist Template\[MMT
> - Watchlist 1-45 Workbook Dec 07 Wk4.xls]Summary'!R6C2:R17C7" _
> ), Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:= _
> False
> Range("B19").Select
>
> It works. BUt when I change that to variables using this code:
>
> Sub Consolidate_Cured()
> '
> Dim strFilePath As String
> Range("B6").Select
> strFilePath = ActiveWorkbook.Path
>
> Selection.Consolidate Sources:=Array( _
> " ' "& strFilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
> Wk1.xls]Summary'!R6C2:R17C7" _
> , _
> " ' "& strFilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
> Wk2.xls]Summary'!R6C2:R17C7" _
> , _
> " ' "& strfilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
> Wk3.xls]Summary'!R6C2:R17C7" _
> ' _
> " ' "& strFilePath &"\"& [MMT - Watchlist 1-45 Workbook Dec 07
> Wk4.xls]Summary'!R6C2:R17C7" _
> ), Function:=xlSum, TopRow:=False, LeftColumn:=false, CreateLinks:= _
> False
>
> Range("B19").Select
> End Sub
>
> and VB return error said :compile error expected: List separator or ).
> Can Anybody help me with the syntax please?
>
> Thank You very3x much...
> Jeff