hello
when I run the code below I receive the error message 9: subscript is out of range.
I'm trying to work with several workbooks using a macro in the personal.xls, and I'm pretty sure that I must have made a mistake in the way I refer to the workbooks. The problem is I've spent so much time over it, that I don't know what to look for. Can anybody help me?
the code:
Code
- Public Sub CreateSheets()
- On Error GoTo METHOD_ERROR
- Const FOREIGN_TEMPLATE_SHEET_NAME As String = "Foreign Subsidiary Code"
- Const US_TEMPLATE_SHEET_NAME As String = "US Parent Code"
- Const COPY_SHEET_NAME As String = "Foreign Subsidiary Code (2)"
- Const COL_INDEX_CODE As Integer = 1
- Const COL_INDEX_NAME As Integer = 2
- Const COL_INDEX_DOMICILE As Integer = 3
- Const ROW_INDEX_START As Integer = 3
- Const ROW_INDEX_US As Integer = 2
- Const RENAME_SHEET_NAME As String = "ForeignEntitySheet"
- Dim nRowIndex As Integer
- Dim sCurrentCode As String
- Dim sCurrentName As String
- Dim sCurrentDomicile As String
- Dim sPreviousCode As String
- Dim wksTemplate As Worksheet
- Dim i As Integer
- Dim sourcewb As Workbook
- Dim currentwb As Workbook
- Dim sourcews As Worksheet
- Dim currentws As Worksheet
- Const adjust As Integer = 1000
- ''BLOCK: Rename US Sheet
- Set wksTemplate = Worksheets(US_TEMPLATE_SHEET_NAME)
- wksTemplate.Name = Cells(ROW_INDEX_US, COL_INDEX_CODE)
- 'initial value of variables
- Set sourcewb = Workbooks("step1+2.xls")
- Set currentwb = ActiveWorkbook
- ''BLOCK: Create Foreign Sheets
- Set wksTemplate = Worksheets(FOREIGN_TEMPLATE_SHEET_NAME)
- sPreviousCode = FOREIGN_TEMPLATE_SHEET_NAME
- nRowIndex = ROW_INDEX_START
- Do
- sCurrentCode = Cells(nRowIndex, COL_INDEX_CODE)
- sCurrentName = Cells(nRowIndex, COL_INDEX_NAME)
- sCurrentDomicile = Cells(nRowIndex, COL_INDEX_DOMICILE)
- If sCurrentCode = vbNullString Then
- Exit Do
- Else
- Worksheets(FOREIGN_TEMPLATE_SHEET_NAME).Copy after:=Worksheets(sPreviousCode)
- Set wksTemplate = Worksheets(COPY_SHEET_NAME)
- wksTemplate.Name = sCurrentCode
- wksTemplate.Cells(3, 1) = sCurrentName
- wksTemplate.Cells(3, 2) = sCurrentDomicile
- For i = 2 To sourcewb.Sheets("Datasource").UsedRange.Columns.Count
- For Each currentws In currentwb.Worksheets
- If currentws.Name = sourcewb.Sheets("Datasource").Cells(12, i).Value Then
- currentws.Cells(4, 2).Value = sourcewb.Sheets("Datasource").Cells(18, i).Value / adjust
- currentws.Cells(5, 2).Value = sourcewb.Sheets("Datasource").Cells(46, i).Value / adjust - sourcewb.Sheets("Datasource").Cells(17, i).Value / adjust
- currentws.Cells(6, 2).Value = sourcewb.Sheets("Datasource").Cells(46, i).Value / adjust
- currentws.Cells(7, 2).Value = sourcewb.Sheets End If
- Next
- Next i
- End If
- nRowIndex = nRowIndex + 1
- sPreviousCode = sCurrentCode
- Loop
- Set wksTemplate = Nothing
- ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\All Users\Desktop\XBS\ImportFinancials.xls", FileFormat _
- :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
- False, CreateBackup:=False
- ''BLOCK: Cleanup
- Application.DisplayAlerts = False
- Worksheets(FOREIGN_TEMPLATE_SHEET_NAME).Delete
- Worksheets(RENAME_SHEET_NAME).Delete
- Exit Sub
- METHOD_ERROR:
- MsgBox "Failed: " & Err.Number & " : " & Err.Description
- End Sub
thanks
K.