Sub Fetch3()
Dim wb As Workbook
Dim ws As Worksheet
Dim CopyVal As String
Dim SheetVal As Range
Dim GTCell As Range
Set CT = Range("CostType")
Set CT2 = Range("CostType2")
Set QRT = Range("Quarter")
Set Yr = Range("StYr")
Set GTCell = Range("GTCell")
Set SheetVal = Range("CtCell")
j = 0
Range("StProj").Offset(j, 0).Activate
Do Until ActiveCell = ""
If GTCell.Value = "Labour Compensation On-Site(Direct)" Then
CopyVal = "C15"
ElseIf GTCell.Value = "Labour Compensation On-Site(Shared)" Then
CopyVal = "C16"
ElseIf GTCell.Value = "Labour Compensation On-Site(Subtotal)" Then
CopyVal = "C17"
ElseIf GTCell.Value = "Labour Compensation Off-Site(Direct)" Then
CopyVal = "C19"
ElseIf GTCell.Value = "Labour Compensation Off-Site(Shared)" Then
CopyVal = "C20"
ElseIf GTCell.Value = "Labour Compensation Off-Site(Subtotal)" Then
CopyVal = "C21"
ElseIf GTCell.Value = "Contract Service" Then
CopyVal = "C23"
ElseIf GTCell.Value = "Equipment Rental" Then
CopyVal = "C24"
ElseIf GTCell.Value = "Purchased Energy (Gas)" Then
CopyVal = "C26"
ElseIf GTCell.Value = "Purchased Energy (Electricity)" Then
CopyVal = "C27"
ElseIf GTCell.Value = "Purchased Energy (Subtotal)" Then
CopyVal = "C28"
ElseIf GTCell.Value = "Energy - Other" Then
CopyVal = "C30"
ElseIf GTCell.Value = "Processing Fees - AL" Then
CopyVal = "C31"
ElseIf GTCell.Value = "Processing Fees - NAL" Then
CopyVal = "C32"
ElseIf GTCell.Value = "Purchased Feedstock - AL" Then
CopyVal = "C33"
ElseIf GTCell.Value = "Purchased Feedstock - NAL" Then
CopyVal = "C34"
ElseIf GTCell.Value = "Supplies and Materials" Then
CopyVal = "C35"
ElseIf GTCell.Value = "Municipal / Provincial Taxes and Fees" Then
CopyVal = "C36"
ElseIf GTCell.Value = "Environmental Levies" Then
CopyVal = "C37"
ElseIf GTCell.Value = "Other" Then
CopyVal = "C38"
ElseIf GTCell.Value = "Total Operating Costs" Then
CopyVal = "C40"
End If
Set ws = ActiveSheet
Set wb = Workbooks.Open("P:...".xls")
wb.Worksheets(SheetVal.Value).Range(CopyVal).Copy
ws.Range("StFill").Offset(j, 0).Paste
wb.Close
j = j + 1
Range("StProj").Offset(j, 0).Activate
Loop
This is a piece of code within a sub I am working on. When I reach the line...
Set wb = Workbooks.Open("P:...".xls")
I am getting runtime error 91. This is odd to me because I have 2 similar subs in my macro that run this exact same line just fine. Any ideas what the problem might be?
Dim wb As Workbook
Dim ws As Worksheet
Dim CopyVal As String
Dim SheetVal As Range
Dim GTCell As Range
Set CT = Range("CostType")
Set CT2 = Range("CostType2")
Set QRT = Range("Quarter")
Set Yr = Range("StYr")
Set GTCell = Range("GTCell")
Set SheetVal = Range("CtCell")
j = 0
Range("StProj").Offset(j, 0).Activate
Do Until ActiveCell = ""
If GTCell.Value = "Labour Compensation On-Site(Direct)" Then
CopyVal = "C15"
ElseIf GTCell.Value = "Labour Compensation On-Site(Shared)" Then
CopyVal = "C16"
ElseIf GTCell.Value = "Labour Compensation On-Site(Subtotal)" Then
CopyVal = "C17"
ElseIf GTCell.Value = "Labour Compensation Off-Site(Direct)" Then
CopyVal = "C19"
ElseIf GTCell.Value = "Labour Compensation Off-Site(Shared)" Then
CopyVal = "C20"
ElseIf GTCell.Value = "Labour Compensation Off-Site(Subtotal)" Then
CopyVal = "C21"
ElseIf GTCell.Value = "Contract Service" Then
CopyVal = "C23"
ElseIf GTCell.Value = "Equipment Rental" Then
CopyVal = "C24"
ElseIf GTCell.Value = "Purchased Energy (Gas)" Then
CopyVal = "C26"
ElseIf GTCell.Value = "Purchased Energy (Electricity)" Then
CopyVal = "C27"
ElseIf GTCell.Value = "Purchased Energy (Subtotal)" Then
CopyVal = "C28"
ElseIf GTCell.Value = "Energy - Other" Then
CopyVal = "C30"
ElseIf GTCell.Value = "Processing Fees - AL" Then
CopyVal = "C31"
ElseIf GTCell.Value = "Processing Fees - NAL" Then
CopyVal = "C32"
ElseIf GTCell.Value = "Purchased Feedstock - AL" Then
CopyVal = "C33"
ElseIf GTCell.Value = "Purchased Feedstock - NAL" Then
CopyVal = "C34"
ElseIf GTCell.Value = "Supplies and Materials" Then
CopyVal = "C35"
ElseIf GTCell.Value = "Municipal / Provincial Taxes and Fees" Then
CopyVal = "C36"
ElseIf GTCell.Value = "Environmental Levies" Then
CopyVal = "C37"
ElseIf GTCell.Value = "Other" Then
CopyVal = "C38"
ElseIf GTCell.Value = "Total Operating Costs" Then
CopyVal = "C40"
End If
Set ws = ActiveSheet
Set wb = Workbooks.Open("P:...".xls")
wb.Worksheets(SheetVal.Value).Range(CopyVal).Copy
ws.Range("StFill").Offset(j, 0).Paste
wb.Close
j = j + 1
Range("StProj").Offset(j, 0).Activate
Loop
This is a piece of code within a sub I am working on. When I reach the line...
Set wb = Workbooks.Open("P:...".xls")
I am getting runtime error 91. This is odd to me because I have 2 similar subs in my macro that run this exact same line just fine. Any ideas what the problem might be?