Hi,
I have two work sheets. The first one (varEoD) contains the data set and the second one varYTD will only be a select 6 columns that I have defined. I want to transfer all rows in varEoD to varYTD that correspond with the 6 column headers. I am getting the "Run Time Error 91: Object variable or with block variable not set" at the line that is in blue below.
I have been staring at this and can not find where I went wrong. Please help!!
Sub YTD_Signed_Sent()
'
' YTD_Signed_Sent Macro
'
'
Dim varSalesRep As Integer
Dim varClient As Integer
Dim varIncRev As Integer
Dim varContractNum As Integer
Dim varSignedDate As Integer
Dim varSigned As Integer
Dim varQtr As Integer
Dim varEoD As Worksheet
Dim varYTD As Worksheet
Dim i As Integer
Dim varLastRow As Integer
Set varEoD = Worksheets("EoD_2015")
Set varYTD = Worksheets("YTD Signed Sent")
'locate important columns
With varEoD.Rows(1)
varSalesRep = .Find("Sales Rep").Column
varClient = .Find("Client").Column
varContractNum = .Find("Contract #").Column
varIncRev = .Find("Incremental Annual Sub Revenue").Column
varSignedDate = .Find("Signed").Column
varSigned = .Find("Signed/Unsigned").Column
varQtr = .Find("Quarter Signed").Column
End With
'clear contents
varYTD.Range("a2:L800").ClearContents
varLastRow = varEoD.Cells(Row.Count, 1).End(xlUp).Row
For i = 2 To varLastRow
a = 2
'finding correct lines
varYTD.Cells(a, 1) = varEoD.Cells(i, varSalesRep)
varYTD.Cells(a, 2) = varEoD.Cells(i, varClient)
varYTD.Cells(a, 3) = varEoD.Cells(i, varContractNum)
varYTD.Cells(a, 4) = varEoD.Cells(i, varSignedDate)
varYTD.Cells(a, 5) = varEoD.Cells(i, varQtr)
varYTD.Cells(a, 6) = varEoD.Cells(i, varSigned)
varYTD.Cells(a, 7) = varEoD.Cells(i, varIncRev)
a = a + 1
Next i
End Sub
I have two work sheets. The first one (varEoD) contains the data set and the second one varYTD will only be a select 6 columns that I have defined. I want to transfer all rows in varEoD to varYTD that correspond with the 6 column headers. I am getting the "Run Time Error 91: Object variable or with block variable not set" at the line that is in blue below.
I have been staring at this and can not find where I went wrong. Please help!!
Sub YTD_Signed_Sent()
'
' YTD_Signed_Sent Macro
'
'
Dim varSalesRep As Integer
Dim varClient As Integer
Dim varIncRev As Integer
Dim varContractNum As Integer
Dim varSignedDate As Integer
Dim varSigned As Integer
Dim varQtr As Integer
Dim varEoD As Worksheet
Dim varYTD As Worksheet
Dim i As Integer
Dim varLastRow As Integer
Set varEoD = Worksheets("EoD_2015")
Set varYTD = Worksheets("YTD Signed Sent")
'locate important columns
With varEoD.Rows(1)
varSalesRep = .Find("Sales Rep").Column
varClient = .Find("Client").Column
varContractNum = .Find("Contract #").Column
varIncRev = .Find("Incremental Annual Sub Revenue").Column
varSignedDate = .Find("Signed").Column
varSigned = .Find("Signed/Unsigned").Column
varQtr = .Find("Quarter Signed").Column
End With
'clear contents
varYTD.Range("a2:L800").ClearContents
varLastRow = varEoD.Cells(Row.Count, 1).End(xlUp).Row
For i = 2 To varLastRow
a = 2
'finding correct lines
varYTD.Cells(a, 1) = varEoD.Cells(i, varSalesRep)
varYTD.Cells(a, 2) = varEoD.Cells(i, varClient)
varYTD.Cells(a, 3) = varEoD.Cells(i, varContractNum)
varYTD.Cells(a, 4) = varEoD.Cells(i, varSignedDate)
varYTD.Cells(a, 5) = varEoD.Cells(i, varQtr)
varYTD.Cells(a, 6) = varEoD.Cells(i, varSigned)
varYTD.Cells(a, 7) = varEoD.Cells(i, varIncRev)
a = a + 1
Next i
End Sub