omnibuster
02-22-2009, 04:56 AM
:banghead:
If i use Sheet Names as Sheet1 Sheet2 ... etc code works good.
But in my workbooks Sheet named As Sht1,Sht2... & AA1 AA2.
gived:
Run-time errer 9
Subscript out of range
In Project Windows I see:
Sheet1(Sht1)
Sheet2(Sht3)
Sheet3(Sht2)
Why dont gived SheetName Sht1 insted Sheet1?
When I sort this sheets (with other code in the modules) code use names Sht1,Sht2...and works good.
Hope you understand what i mean.
Sub CopyModule_Sht2_toSht1() 'Selle tööraamatu makro kopib töölehe nr2 makro töölehele nr 1
Dim wbDest As Workbook, wbSource As Workbook, v_clsDest As VBComponent, v_clsSource As VBComponent, _
clsM_Dest As CodeModule, clsM_Source As CodeModule, lCnt As Long, lStart As Long, lBeg As Long
Dim Sht As Worksheet
Set wbDest = ActiveWorkbook
Set wbSource = ActiveWorkbook
Set v_clsDest = wbDest.VBProject.VBComponents("Sht1") 'iF name Sheet1-works good.
Set v_clsSource = wbSource.VBProject.VBComponents("Sht2") 'iF name Sheet2-works good.
Set clsM_Dest = v_clsDest.CodeModule
Set clsM_Source = v_clsSource.CodeModule
For lCnt = 1 To clsM_Source.CountOfDeclarationLines
If InStr(1, clsM_Source.Lines(lCnt, 1), _
"Option Explicit", vbTextCompare) = 0 Then
lBeg = lBeg + 1
End If
Next
lBeg = clsM_Source.CountOfDeclarationLines - lBeg
lStart = clsM_Source.CountOfLines
For lCnt = 1 To clsM_Source.CountOfLines
clsM_Dest.InsertLines lCnt + lStart, clsM_Source.Lines(lCnt + lBeg, 1)
Next
Set wbDest = Nothing
Set wbSource = Nothing
Set v_clsDest = Nothing
Set v_clsSource = Nothing
Set clsM_Dest = Nothing
Set clsM_Source = Nothing
End Sub
If i use Sheet Names as Sheet1 Sheet2 ... etc code works good.
But in my workbooks Sheet named As Sht1,Sht2... & AA1 AA2.
gived:
Run-time errer 9
Subscript out of range
In Project Windows I see:
Sheet1(Sht1)
Sheet2(Sht3)
Sheet3(Sht2)
Why dont gived SheetName Sht1 insted Sheet1?
When I sort this sheets (with other code in the modules) code use names Sht1,Sht2...and works good.
Hope you understand what i mean.
Sub CopyModule_Sht2_toSht1() 'Selle tööraamatu makro kopib töölehe nr2 makro töölehele nr 1
Dim wbDest As Workbook, wbSource As Workbook, v_clsDest As VBComponent, v_clsSource As VBComponent, _
clsM_Dest As CodeModule, clsM_Source As CodeModule, lCnt As Long, lStart As Long, lBeg As Long
Dim Sht As Worksheet
Set wbDest = ActiveWorkbook
Set wbSource = ActiveWorkbook
Set v_clsDest = wbDest.VBProject.VBComponents("Sht1") 'iF name Sheet1-works good.
Set v_clsSource = wbSource.VBProject.VBComponents("Sht2") 'iF name Sheet2-works good.
Set clsM_Dest = v_clsDest.CodeModule
Set clsM_Source = v_clsSource.CodeModule
For lCnt = 1 To clsM_Source.CountOfDeclarationLines
If InStr(1, clsM_Source.Lines(lCnt, 1), _
"Option Explicit", vbTextCompare) = 0 Then
lBeg = lBeg + 1
End If
Next
lBeg = clsM_Source.CountOfDeclarationLines - lBeg
lStart = clsM_Source.CountOfLines
For lCnt = 1 To clsM_Source.CountOfLines
clsM_Dest.InsertLines lCnt + lStart, clsM_Source.Lines(lCnt + lBeg, 1)
Next
Set wbDest = Nothing
Set wbSource = Nothing
Set v_clsDest = Nothing
Set v_clsSource = Nothing
Set clsM_Dest = Nothing
Set clsM_Source = Nothing
End Sub