aisantoso63
04-23-2009, 11:36 PM
I need help re: worksheets name. my vba is creating several worksheets, and when I want to rename it, even I use "for..next" loop, it looks like it doesn't start from small number.
My for loop like below :
max = row1 'the last of index as max count of sheets to create
'Excel by default comes with 3 worksheets, so I just add some
If max > 3 Then
Sheets.Add Count:=max - 4
End If
row = 3
For i = 2 To max - 2
row1 = 3
With Sheets(SheetName1)
Sheets(i).Name = .Cells(row, 8)
SheetName2 = Sheets(i).Name
For j = .Cells(row, 9) To .Cells(row, 10)
Sheets(SheetName2).Cells(row1, 1) = .Cells(j, 2)
Sheets(SheetName2).Cells(row1, 2) = .Cells(j, 3)
Sheets(SheetName2).Cells(row1, 3) = .Cells(j, 4)
Sheets(SheetName2).Cells(row1, 4) = .Cells(j, 5)
Sheets(SheetName2).Cells(row1, 5) = .Cells(j, 6)
row1 = row1 + 1
Next j
If row = max + 2 Then
Exit For
End If
row = row + 1
End With
Next i
i put if block in max+2 because I'm bit confusing with the numbering here.
But, my main issue is even my sheets(1).name is also renamed. can anyone help me how to make this worksheets sequentially renamed?
thanks,
goose
My for loop like below :
max = row1 'the last of index as max count of sheets to create
'Excel by default comes with 3 worksheets, so I just add some
If max > 3 Then
Sheets.Add Count:=max - 4
End If
row = 3
For i = 2 To max - 2
row1 = 3
With Sheets(SheetName1)
Sheets(i).Name = .Cells(row, 8)
SheetName2 = Sheets(i).Name
For j = .Cells(row, 9) To .Cells(row, 10)
Sheets(SheetName2).Cells(row1, 1) = .Cells(j, 2)
Sheets(SheetName2).Cells(row1, 2) = .Cells(j, 3)
Sheets(SheetName2).Cells(row1, 3) = .Cells(j, 4)
Sheets(SheetName2).Cells(row1, 4) = .Cells(j, 5)
Sheets(SheetName2).Cells(row1, 5) = .Cells(j, 6)
row1 = row1 + 1
Next j
If row = max + 2 Then
Exit For
End If
row = row + 1
End With
Next i
i put if block in max+2 because I'm bit confusing with the numbering here.
But, my main issue is even my sheets(1).name is also renamed. can anyone help me how to make this worksheets sequentially renamed?
thanks,
goose