Need some help on the Function

Hi Guys,

I tried working on a macro for a repetitive task. It was working until i tried simplifying it and ruined it entirely. Now when i run this macro I get an error: "Function call on left hand side of assignment must return variant or object"

[vba]Public Function lr() As Long
lr = ActiveSheet.Cells.Find("*", , , , xlByRows, xlPrevious).Row
End Function
Public Function lc() As Long
lc = ActiveSheet.Cells.Find("*", , , , xlByColumns, xlPrevious).Column
End Function
Sub CORI_HC()
'
' Macro1 Macro
' Macro by Dilip Ramadas
'BIEN
Dim Path1, Path2, path3, path4, path5, path6, path7, path8, path9, path10, path11, path12, path13, path14, path15 As String
Path1 = Sheet1.Range("d2").Value
Path2 = Sheet1.Range("d3").Value
path3 = Sheet1.Range("d4").Value
path4 = Sheet1.Range("d7").Value
path5 = Sheet1.Range("d8").Value
path6 = Sheet1.Range("d9").Value
path7 = Sheet1.Range("d12").Value
path8 = Sheet1.Range("d13").Value
path9 = Sheet1.Range("d14").Value
path10 = Sheet1.Range("d17").Value
path11 = Sheet1.Range("d18").Value
path12 = Sheet1.Range("d19").Value
path13 = Sheet1.Range("d22").Value
path14 = Sheet1.Range("d23").Value
path15 = Sheet1.Range("d24").Value
Dim file1, file2, file3, file4, file5, file6, file7, file8, file9, file10, file11, file12, file13, file14, file15 As String
file1 = Sheet1.Range("e2").Value
file2 = Sheet1.Range("e3").Value
file3 = Sheet1.Range("e4").Value
file4 = Sheet1.Range("e7").Value
file5 = Sheet1.Range("e8").Value
file6 = Sheet1.Range("e9").Value
file7 = Sheet1.Range("e12").Value
file8 = Sheet1.Range("e13").Value
file9 = Sheet1.Range("e14").Value
file10 = Sheet1.Range("e17").Value
file11 = Sheet1.Range("e18").Value
file12 = Sheet1.Range("e19").Value
file13 = Sheet1.Range("e22").Value
file14 = Sheet1.Range("e23").Value
file15 = Sheet1.Range("e24").Value

Workbooks.Open Filename:= _
Path1 & file1
wb1 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
Path2 & file2
wb2 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
path3 & file3
wb3 = ActiveWorkbook.Name

Windows(wb3).Activate

Sheets("Source").Select
lr = Range("A5000").End(xlUp).Row

If lr > 1 Then
Rows("2:" & lr).Delete
End If

Windows(wb1).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy
Windows(wb3).Activate
Rows("2:2").Select
ActiveSheet.Paste

Windows(wb2).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy

Windows(wb3).Activate
lr = Range("A5000").End(xlUp).Row + 1
Rows(lr).Select
ActiveSheet.Paste
Range("A1").Select
lr = Range("A5000").End(xlUp).Row
Dim pvtrng As Range
Set pvtrng = Range("A1:CS" & lr)
ActiveSheet.Previous.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
pvtrng, Version:=xlPivotTableVersion12)

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close

'cba
Workbooks.Open Filename:= _
path4 & file4
wb4 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
path5 & file5
wb5 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
path6 & file6
wb6 = ActiveWorkbook.Name

Windows(wb6).Activate

Sheets("Source").Select
lr = Range("A5000").End(xlUp).Row

If lr > 1 Then
Rows("2:" & lr).Delete
End If

Windows(wb4).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy
Windows(wb6).Activate
Rows("2:2").Select
ActiveSheet.Paste

Windows(wb5).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy

Windows(wb6).Activate
lr = Range("A5000").End(xlUp).Row + 1
Rows(lr).Select
ActiveSheet.Paste
Range("A1").Select
lr = Range("A5000").End(xlUp).Row
Dim pvtrng1 As Range
Set pvtrng1 = Range("A1:CS" & lr)
ActiveSheet.Previous.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
pvtrng1, Version:=xlPivotTableVersion12)

ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close

'CORI Cov
Workbooks.Open Filename:= _
path7 & file7
wb7 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
path8 & file8
wb8 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
path9 & file9
wb9 = ActiveWorkbook.Name

Windows(wb9).Activate

Sheets("Source").Select
lr = Range("A5000").End(xlUp).Row

If lr > 1 Then
Rows("2:" & lr).Delete
End If

Windows(wb7).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy
Windows(wb9).Activate
Rows("2:2").Select
ActiveSheet.Paste

Windows(wb8).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy

Windows(wb9).Activate
lr = Range("A5000").End(xlUp).Row + 1
Rows(lr).Select
ActiveSheet.Paste
Range("A1").Select
lr = Range("A5000").End(xlUp).Row
Dim pvtrng2 As Range
Set pvtrng2 = Range("A1:CS" & lr)
ActiveSheet.Previous.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable4").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
pvtrng2, Version:=xlPivotTableVersion12)

ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close

'JV MARK
Workbooks.Open Filename:= _
path10 & file10
wb10 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
path11 & file11
wb11 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
path12 & file12
wb12 = ActiveWorkbook.Name

Windows(wb12).Activate

Sheets("Source").Select
lr = Range("A5000").End(xlUp).Row

If lr > 1 Then
Rows("2:" & lr).Delete
End If

Windows(wb10).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy
Windows(wb12).Activate
Rows("2:2").Select
ActiveSheet.Paste

Windows(wb11).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy

Windows(wb12).Activate
lr = Range("A5000").End(xlUp).Row + 1
Rows(lr).Select
ActiveSheet.Paste
Range("A1").Select
lr = Range("A5000").End(xlUp).Row
Dim pvtrng3 As Range
Set pvtrng3 = Range("A1:CS" & lr)
ActiveSheet.Previous.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
pvtrng3, Version:=xlPivotTableVersion12)

ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close

'MNA
Workbooks.Open Filename:= _
path13 & file13
wb13 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
path14 & file14
wb14 = ActiveWorkbook.Name

Workbooks.Open Filename:= _
path15 & file15
wb15 = ActiveWorkbook.Name

Windows(wb15).Activate

Sheets("Source").Select
lr = Range("A5000").End(xlUp).Row

If lr > 1 Then
Rows("2:" & lr).Delete
End If

Windows(wb13).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy
Windows(wb15).Activate
Rows("2:2").Select
ActiveSheet.Paste

Windows(wb14).Activate
lr = Range("A5000").End(xlUp).Row
Rows("2:" & lr).Copy

Windows(wb15).Activate
lr = Range("A5000").End(xlUp).Row + 1
Rows(lr).Select
ActiveSheet.Paste
Range("A1").Select
lr = Range("A5000").End(xlUp).Row
Dim pvtrng4 As Range
Set pvtrng4 = Range("A1:CS" & lr)
ActiveSheet.Previous.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable6").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
pvtrng4, Version:=xlPivotTableVersion12)

ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
End Sub
[/vba]

I've been trying to crack my head for this but without any luck so far.
Any help would be appreciated. Thanks in advance.

Regards,
Dilip :banghead:

You named the Function "lc" the same as a Variable "lc" in the other Sub.

Tip: Use
[vba]Option Explicit[/vba] as the first line of code.

Tip2: When using Dim, note that you must define each type. e.g.
[vba]Dim lc, lr as long[/vba] This defines lc as a variant and lr as Long.

Tip3. Column numbers are Integers. Row numbers are Long.