Excel 2000 and VB 6 coding problem
I have the following code, but I can not figure out how to insert the row I just copied, here's what I have:
Code:
Public Function copyRowTemplate(Workbook As Excel.Workbook, workSheet As String, strCol As String, RowName As String, TabRef As String, RowTemplate As String, RowToCopy As Long) As Boolean
Dim strTemplate As String
Dim lngRow As Long
Dim StartCol As Long
Dim endCol As Long
Workbook.Sheets(workSheet).Unprotect
endCol = Asc("J")
Workbook.Sheets(workSheet).Rows(RowToCopy & ":" & RowToCopy).Copy
Workbook.Sheets(workSheet).Insert Shift:=xlDown 'this is where the error is ocurring
Workbook.Sheets(workSheet).Range(strCol & RowToCopy).Value = RowName
For StartCol = Asc("B") To endCol
strTemplate = Workbook.Sheets(workSheet).Range(Chr(StartCol) & RowToCopy).Value
strTemplate = Replace(strTemplate, "&&SHORTNAME&&", "'" & TabRef & "'")
strTemplate = "=" & strTemplate
Workbook.Sheets(workSheet).Range(Chr(StartCol) & RowToCopy).Value = strTemplate
'strTemplate = objWorksheet.Range(Chr(StartCol) & RowToCopy).Value
Next
Workbook.Sheets(workSheet).Range (strCol & RowToCopy)
Workbook.Sheets(workSheet).Select
Workbook.Sheets(workSheet).EntireRow.Hidden = False
Workbook.Sheets(workSheet).Range (strCol & RowToCopy + 1)
Workbook.Sheets(workSheet).Select
Workbook.Sheets(workSheet).EntireRow.Hidden = True
copyRowTemplate = True
Workbook.Sheets(workSheet).Protect
Exit Function
HandleError:
copyRowTemplate = False
End Function
I know how to do it using the excel.worksheet object and an excel.range object, but I want to avoid using them to save some time...