catharsis50
New Member
- Joined
- Nov 1, 2011
- Messages
- 46
I am very new to Vb and have got myself into what is a relatively big project for myself. I get the runtime error '91' object varaible or with block variable not set when running this code. I have just pasted the portion that causes the error. Before this portion of the script it is being executed in another workbook, then opening this workbook and continuing to run the script.
This is also causing the Excel Addin for Salesforce.com to not allow the computer to login. Any help is very much appreciated. Let me know if you need any more of the script.
Thanks!
'Workbooks.Open Filename:="C:\Users\msalget\Documents\Metrics\Bi Weekly Metrics\Q3 QBR Metrics Report Template"
'--Activate if not using refresh
'Workbooks("Q3 QBR Metrics Report Template.xlsm").Sheets("Karl Dashboard").Activate
Dim LastRow3 As Long, LastRow4 As Long
LastRow3 = Cells(Rows.Count, "A").End(xlUp).Row
LastRow4 = LastRow3 - 1
Dim row3 As Long, row4 As Long, rrow4 As Long, diff1 As Long
row3 = Sheets("Deduped Campaign Data").Cells(Rows.Count, "A").End(xlUp).Row
row4 = Sheets("Dashboard").Cells(Rows.Count, "A").End(xlUp).Row
rrow4 = row4 - 1
diff1 = row3 - rrow4
'--Refresh SFDC reports
'VBLogin = SFDCExcelAddin.IsLoggedIn() '--check if login was successfull
'SFDCExcelAddin.RefreshAll
'Sheets("Campaign with Campaign Members").Select
' WithCselect.Select
' Selection.Copy
' Sheets("Deduped Lead Contact Data").Select
' Range("A1").Select
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Application.CutCopyMode = False
'--Autofill all formulas for Column AK & AN
'Range("AK2:AN2").AutoFill Destination:=Range("AK2:AN" & LastRow)
'ActiveWorkbook.Worksheets("Deduped Lead Contact Data").AutoFilter.Sort. _
SortFields.Add Key:=Range("AN1"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
'ActiveSheet.Range("$A$1:AN" & LastRow).RemoveDuplicates Columns:=23, Header:= _
xlYes
' With ActiveWorkbook.Worksheets("Deduped Lead Contact Data").AutoFilter.Sort
' .Header = xlYes
' .MatchCase = False
' .Orientation = xlTopToBottom
' .SortMethod = xlPinYin
' .Apply
' End With
'--Old DedupCampaigns Macro
Dim WwithC, Wwith1, Wwithselect As Range
Set WwithC = Sheets("Campaign with Campaign Members").Range("AE1").End(xlDown).Offset(0, 2)
Set WwithC1 = Sheets("Campaign with Campaign Members").Range("B1")
Set WwithCselect = Range(WwithC1, WwithC)
Dim DCD, DCD1, DCDselect As Range
Set DCD = Sheets("DeDuped Campaign Data").Range("AD1").End(xlDown).Offset(0, 2)
Set DCD1 = Sheets("DeDuped Campaign Data").Range("A1")
Set DCDselect = Range(DCD1, DCD)
'Sheets("Campaign with Campaign Members").Select
'WwithCselect.Select
'Selection.Copy
'Sheets("Deduped Campaign Data").Select
'Range("A1").Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'DCDselect.RemoveDuplicates Columns:=1, Header:=xlYes
'--Copy data from Campaigns wo Campaign Members
Dim CWCM, CWCM1, CWCMselect As Range
Set CWCM = Sheets("Campaigns wo Campaign Members").Range("AD2").End(xlDown).Offset(0, 1)
Set CWCM1 = Sheets("Campaigns wo Campaign Members").Range("A2")
Set CWCMselect = Range(CWCM1, CWCM)
Dim NewDD, NewDD1, NewDDselect As Range
Set NewDD = Sheets("DeDuped Campaign Data").Range("AD1").End(xlDown).Offset(0, 2)
Set NewDD1 = Sheets("DeDuped Campaign Data").Range("A1")
Set NewDDselect = Range(NewDD1, NewDD)
'Sheets("Campaigns wo Campaign Members").Select
'CWCMselect.Select
'Selection.Copy
'Sheets("Deduped Campaign Data").Select
'Range("A1").End(xlDown).Select
'ActiveCell.Offset(1, 0).Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Range("A1").Select
'NewDDselect.Select
'Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'sPercentage = 60
'IncrementalProgress.Increment sPercentage, sStatus
'--Dedupe Opps in Mktg Influenced Opps based on Opp ID
Dim MKops, MKops1, MKopsselect As Range
Set MKops = Sheets("Mktg influenced opps").Range("AA1").End(xlDown)
Set MKops1 = Sheets("Mktg influenced opps").Range("A1")
Set MKopsselect = Range(MKops1, MKops)
'Sheets("Mktg Influenced Opps").Select
'MKopsselect.RemoveDuplicates Columns:=4, Header:=xlYes
'--Insert New rows into Dashbord table
'Sheets("Dashboard").Select
'Range("A2").End(xlDown).Select
'ActiveCell.Offset(-1, 0).Select
'ActiveCell.Offset(1, 0).EntireRow.Resize(diff).Insert
'Range("A2:S2").AutoFill Destination:=Range("A2:S" & row1), Type:=xlFillDefault
'Range("A2:S" & row1).Select
'Selection.Borders(xlDiagonalDown).LineStyle = xlNone
' Selection.Borders(xlDiagonalUp).LineStyle = xlNone
' Selection.Borders(xlEdgeLeft).LineStyle = xlNone
' With Selection.Borders(xlEdgeTop)
' .LineStyle = xlContinuous
' .ColorIndex = 0
' .TintAndShade = 0
' .Weight = xlMedium
' End With
' Selection.Borders(xlEdgeBottom).LineStyle = xlNone
' Selection.Borders(xlEdgeRight).LineStyle = xlNone
' Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'--Copy and Paste Dashboard to Campaign Summary data
Dim CSD, CSD1, CSDselect As Range
Set CSD = Sheets("Campaign Summary Data").Range("S1").End(xlDown)
Set CSD1 = Sheets("Campaign Summary Data").Range("A1")
Set CSDselect = Range(CSD1, CSD)
'Sheets("Dashboard").Select
'Range("A1:S" & LastRow1).Select
'Selection.Copy
'Sheets("Campaign Summary Data").Select
'Range("A1").Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'CSDselect.RemoveDuplicates Columns:=3, Header:=xlYes
This is also causing the Excel Addin for Salesforce.com to not allow the computer to login. Any help is very much appreciated. Let me know if you need any more of the script.
Thanks!
'Workbooks.Open Filename:="C:\Users\msalget\Documents\Metrics\Bi Weekly Metrics\Q3 QBR Metrics Report Template"
'--Activate if not using refresh
'Workbooks("Q3 QBR Metrics Report Template.xlsm").Sheets("Karl Dashboard").Activate
Dim LastRow3 As Long, LastRow4 As Long
LastRow3 = Cells(Rows.Count, "A").End(xlUp).Row
LastRow4 = LastRow3 - 1
Dim row3 As Long, row4 As Long, rrow4 As Long, diff1 As Long
row3 = Sheets("Deduped Campaign Data").Cells(Rows.Count, "A").End(xlUp).Row
row4 = Sheets("Dashboard").Cells(Rows.Count, "A").End(xlUp).Row
rrow4 = row4 - 1
diff1 = row3 - rrow4
'--Refresh SFDC reports
'VBLogin = SFDCExcelAddin.IsLoggedIn() '--check if login was successfull
'SFDCExcelAddin.RefreshAll
'Sheets("Campaign with Campaign Members").Select
' WithCselect.Select
' Selection.Copy
' Sheets("Deduped Lead Contact Data").Select
' Range("A1").Select
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Application.CutCopyMode = False
'--Autofill all formulas for Column AK & AN
'Range("AK2:AN2").AutoFill Destination:=Range("AK2:AN" & LastRow)
'ActiveWorkbook.Worksheets("Deduped Lead Contact Data").AutoFilter.Sort. _
SortFields.Add Key:=Range("AN1"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
'ActiveSheet.Range("$A$1:AN" & LastRow).RemoveDuplicates Columns:=23, Header:= _
xlYes
' With ActiveWorkbook.Worksheets("Deduped Lead Contact Data").AutoFilter.Sort
' .Header = xlYes
' .MatchCase = False
' .Orientation = xlTopToBottom
' .SortMethod = xlPinYin
' .Apply
' End With
'--Old DedupCampaigns Macro
Dim WwithC, Wwith1, Wwithselect As Range
Set WwithC = Sheets("Campaign with Campaign Members").Range("AE1").End(xlDown).Offset(0, 2)
Set WwithC1 = Sheets("Campaign with Campaign Members").Range("B1")
Set WwithCselect = Range(WwithC1, WwithC)
Dim DCD, DCD1, DCDselect As Range
Set DCD = Sheets("DeDuped Campaign Data").Range("AD1").End(xlDown).Offset(0, 2)
Set DCD1 = Sheets("DeDuped Campaign Data").Range("A1")
Set DCDselect = Range(DCD1, DCD)
'Sheets("Campaign with Campaign Members").Select
'WwithCselect.Select
'Selection.Copy
'Sheets("Deduped Campaign Data").Select
'Range("A1").Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'DCDselect.RemoveDuplicates Columns:=1, Header:=xlYes
'--Copy data from Campaigns wo Campaign Members
Dim CWCM, CWCM1, CWCMselect As Range
Set CWCM = Sheets("Campaigns wo Campaign Members").Range("AD2").End(xlDown).Offset(0, 1)
Set CWCM1 = Sheets("Campaigns wo Campaign Members").Range("A2")
Set CWCMselect = Range(CWCM1, CWCM)
Dim NewDD, NewDD1, NewDDselect As Range
Set NewDD = Sheets("DeDuped Campaign Data").Range("AD1").End(xlDown).Offset(0, 2)
Set NewDD1 = Sheets("DeDuped Campaign Data").Range("A1")
Set NewDDselect = Range(NewDD1, NewDD)
'Sheets("Campaigns wo Campaign Members").Select
'CWCMselect.Select
'Selection.Copy
'Sheets("Deduped Campaign Data").Select
'Range("A1").End(xlDown).Select
'ActiveCell.Offset(1, 0).Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Range("A1").Select
'NewDDselect.Select
'Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'sPercentage = 60
'IncrementalProgress.Increment sPercentage, sStatus
'--Dedupe Opps in Mktg Influenced Opps based on Opp ID
Dim MKops, MKops1, MKopsselect As Range
Set MKops = Sheets("Mktg influenced opps").Range("AA1").End(xlDown)
Set MKops1 = Sheets("Mktg influenced opps").Range("A1")
Set MKopsselect = Range(MKops1, MKops)
'Sheets("Mktg Influenced Opps").Select
'MKopsselect.RemoveDuplicates Columns:=4, Header:=xlYes
'--Insert New rows into Dashbord table
'Sheets("Dashboard").Select
'Range("A2").End(xlDown).Select
'ActiveCell.Offset(-1, 0).Select
'ActiveCell.Offset(1, 0).EntireRow.Resize(diff).Insert
'Range("A2:S2").AutoFill Destination:=Range("A2:S" & row1), Type:=xlFillDefault
'Range("A2:S" & row1).Select
'Selection.Borders(xlDiagonalDown).LineStyle = xlNone
' Selection.Borders(xlDiagonalUp).LineStyle = xlNone
' Selection.Borders(xlEdgeLeft).LineStyle = xlNone
' With Selection.Borders(xlEdgeTop)
' .LineStyle = xlContinuous
' .ColorIndex = 0
' .TintAndShade = 0
' .Weight = xlMedium
' End With
' Selection.Borders(xlEdgeBottom).LineStyle = xlNone
' Selection.Borders(xlEdgeRight).LineStyle = xlNone
' Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'--Copy and Paste Dashboard to Campaign Summary data
Dim CSD, CSD1, CSDselect As Range
Set CSD = Sheets("Campaign Summary Data").Range("S1").End(xlDown)
Set CSD1 = Sheets("Campaign Summary Data").Range("A1")
Set CSDselect = Range(CSD1, CSD)
'Sheets("Dashboard").Select
'Range("A1:S" & LastRow1).Select
'Selection.Copy
'Sheets("Campaign Summary Data").Select
'Range("A1").Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'CSDselect.RemoveDuplicates Columns:=3, Header:=xlYes