dilipramadas
10-31-2011, 05:48 AM
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"
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
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:
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"
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
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: