Code added by VBA ends up on wrong sheet?
I am trying to add a little bit of code to a worksheet in a workbook that is being created by VBA... actually, I take that back, the workbook is actually created by a Make-Table query in Access, which exports it to a new workbook.
I open the workbook as the ReportList workbook object, fiddle with the data a bit, then add a new worksheet object "ReportCusts". I then try to add the code to the ReportCusts worksheet object... and the code puts it on the first worksheet instead of the second.
The bit of code in question...
Code:
Set ReportCusts = Worksheets.Add(Before:=Sheets(1))
ReportList.VBProject.VBComponents(ReportCusts.Name).CodeModule. _
AddFromString _
"Private Sub Worksheet_Change(ByVal Target As Range)" & Chr(10) & _
" If Cells(1, Target.Column).Value = ""Select"" Then" & Chr(10) & _
" Target.Value = UCase(Target.Value)" & Chr(10) & _
" End If" & Chr(10) & _
"End Sub"
At the time the attempt to add the code is made, the newly-added sheet (named "Sheet1") shows as Sheet2 in the VBE Project Explorer window, with the original sheet being Sheet1.
I have tried changing "VBComponents(ReportCusts.Name)" to "VBComponents("Sheet1"), and the code ends up on the original first sheet.
I have tried changing "VBComponents(ReportCusts.Name)" to "VBComponents(Sheets(2).Name)" or "VBComponents(ReportList.Sheets(2).Name)", and I get a "Subscript out of range" error.
Suggestions?
