I am facing this problem on the On Error GoTo statement. It is
located inside a loop. During the first run it when the error occurs
it performs OK and goes to the GoTo label. But during the second
time when it encounters the error the code fails, that means it
doesn't go to the GoTo label and the run time error pops up. I am
attaching the code below. As you can see I have tried to put the On
Error and GoTo statement at many places. But every time I have had
the run time error pop up during the second run.
As you can see the code is not very well structured. I am begginer
to VBA and try to learn it improve my efficiency in Excel. Your
inputs on more structured code would also help.
Sub populateEmpElasticityTable()
Dim colAdjR2, colObs, colSlope, colIntcpt, colTstat
colAdjR2 = 2
colObs = 2
colSlope = 2
colIntcpt = 2
colTstat = 4
Dim rowAdjR2, rowObs, rowSlope, rowIntcpt, rowTstat
rowAdjR2 = 6
rowObs = 8
rowSlope = 18
rowIntcpt = 17
rowTstat = 18

'create the sheet name
'read the sheet for the specified cells
'and write them to the specified sheets
Dim wbTo, wbFrom1, wbFrom2 As Workbook
Set wbTo = Workbooks("EmpElasticityTable.xls")
Set wbFrom2 = Workbooks("elasticityBook2Regression2.xls")
Set wbFrom1 = Workbooks("elasticityBook2Regression.xls")
Dim shtTo As Worksheet
Set shtTo = wbTo.Worksheets("Consolidate")
Dim rowTo
rowTo = 3
Dim colTo
colTo = 5
Dim shtName As String
Dim shtName1 As String
Dim shtName2 As String
shtName1 = shtTo.Cells(1, colTo)
shtName2 = shtTo.Cells(rowTo, 1) & shtTo.Cells(rowTo, 2)
shtName = shtName1 + "-" + shtName2
'MsgBox shtName
Dim shtFrom As Worksheet
'On Error GoTo BadSheetName
Do While shtTo.Cells(rowTo, 1) <> Empty
'On Error GoTo BadSheetName
Do While shtTo.Cells(1, colTo) <> Empty
MsgBox shtName
On Error GoTo BadSheetName
'On Error Resume Next
Set shtFrom = wbFrom1.Worksheets(shtName)
'On Error GoTo BadSheetName
MsgBox shtName
MsgBox shtFrom.Cells(rowAdjR2, colAdjR2).Value

shtTo.Cells(rowTo, colTo).Value = shtFrom.Cells(rowAdjR2,
colAdjR2).Value

shtTo.Cells(rowTo, colTo + 1).Value = shtFrom.Cells(rowObs,
colObs).Value

shtTo.Cells(rowTo, colTo + 2).Value = shtFrom.Cells(rowIntcpt,
colIntcpt).Value

shtTo.Cells(rowTo, colTo + 3).Value = shtFrom.Cells(rowSlope,
colSlope).Value

shtTo.Cells(rowTo, colTo + 4).Value = shtFrom.Cells(rowTstat,
colTstat).Value

'BadSheetName:
'On Error GoTo 0
'MsgBox "Out"
colTo = colTo + 5
shtName1 = shtTo.Cells(1, colTo)
shtName2 = shtTo.Cells(rowTo, 1) & shtTo.Cells(rowTo, 2)
shtName = shtName1 + "-" + shtName2
Loop

colTo = 5
rowTo = rowTo + 1
shtName1 = shtTo.Cells(1, colTo)
shtName2 = shtTo.Cells(rowTo, 1) & shtTo.Cells(rowTo, 2)
shtName = shtName1 + "-" + shtName2
BadSheetName:
On Error GoTo 0
MsgBox "Out"
colTo = colTo + 5
shtName1 = shtTo.Cells(1, colTo)
shtName2 = shtTo.Cells(rowTo, 1) & shtTo.Cells(rowTo, 2)
shtName = shtName1 + "-" + shtName2
Loop

MsgBox "done"

End Sub