Private Sub Cmb_Generate_Click()
Dim Rng As Range, W As Long, w1 As Long, aw As Long, hal As Long
w1 = 1
aw = 0
hal = 1
Set WAdd = ActiveWorkbook
'Set Rng = WAdd.Sheets(1).Range("b2")
'Set Rng = WAdd.Sheets(1).Range(Rng, Rng.End(xlDown))
'diganti menjadi
WAdd.Sheets("sheet1").Activate
Set Rng = ActiveSheet.Range("b2")
If Rng.Value = "" Then
MsgBox "data kosong"
Exit Sub
End If
Set Rng = ActiveSheet.Range(Rng, Rng.End(xlDown))
' batas pengantian For W = 1 To Rng.Rows.Count
' If W Mod 30 = 0 Or w1 = 1 Then
If W Mod 30 = 0 Or hal = 1 Then' supaya 30 baris ganti dng If
((W-1) Mod 30 = 0) Or hal = 1 Then
' ThisWorkbook.Sheets("SPKL").Copy Before:=WAdd.Sheets(1)
' Set SAdd = ActiveSheet
' SAdd.Name = "SPKL" & hal'diganti menjadi
On Error Resume Next
Worksheets("SPKL" & hal).Activate
If Err.Number = 9 Then
MsgBox "Error maka buat " & hal
ThisWorkbook.Sheets("SPKL").Copy Before:=WAdd.Sheets(1)
Set SAdd = ActiveSheet
SAdd.Name = "SPKL" & hal
Else
Set SAdd = ActiveSheet
End If
On Error GoTo 0
' batas pengantian
WAdd.Sheets(SAdd.Name).Range("i5") = cmb_area.Value
WAdd.Sheets(SAdd.Name).Range("i6") = txt_atasan.Value
WAdd.Sheets(SAdd.Name).Range("C6") = lbl_tgl.Caption
WAdd.Sheets(SAdd.Name).Range("C41") = Rng.Rows.Count
'WAdd.Sheets(SAdd.Name).Range("i60") = (WorksheetFunction.Ceiling(W, 29) / 29) _
& " Dari " & (WorksheetFunction.Ceiling(Rng.Rows.Count, 29) / 29)
' w1 = w1 + 1 ' bisa digunakan hal saja
aw = 1
'isi tiap hal/sheet adalah 29
hal = hal + 1' agar isi sebelumnya dihapus
WAdd.Sheets(SAdd.Name).Range("A10").Select
Range(Selection, Selection.Offset(29, 9)).ClearContents
End If
With WAdd.Sheets(SAdd.Name).Range("A10")
.Cells(aw, 1) = W
.Cells(aw, 3) = Format(txt_scan.Value, "'000000")
.Cells(aw, 6) = Left(txt_jam, 5)
.Cells(aw, 7) = Right(txt_jam, 5)
End With
' baris berikutnya
aw = aw + 1
NextEnd Sub
To: [email protected]
From: [email protected]
Date: Mon, 5 Dec 2011 16:02:25 +0700
Subject: Re: [belajar-excel] Debug dan restart my workbook
Mr Kid setelah saya Replace code yang lama
dengan yang di buatkan Mr kid
ternyata masih error pada bagian ThisWorkbook.Sheets(sSht).Copy
before:=WAdd.Sheets(1)
mohon bantuannya para pakar Excel
Terimakasih
Best
Regard
<>.<>.<>.<>.<>.<>.
Sidoel
----- Original Message -----
From:
Kid Mr.
To: [email protected]
Sent: Monday, December 05, 2011 3:33
PM
Subject: Re: [belajar-excel] Debug dan
restart my workbook
hehehe...
ternyata ada attachment yang disusulkan.
Kira-kira
scriptnya demikian :
Private Sub
Cmb_Generate_Click()
Dim Rng As
Range
Dim sSht As String
Dim W As
Long, w1 As Long, aw As Long, hal As Long
Dim
lRecPerPage As Long, lTotalPage As Long
'aw =
0
'hal = 1
'init
object kerja
Set WAdd =
ActiveWorkbook
Set Rng =
WAdd.Sheets(1).Range("b2").CurrentRegion
w1 =
Rng.Rows.Count - 1
If w1 < 1
Then
Exit
Sub
End If
Set Rng =
Rng.Offset(1).Resize(w1)
'init
konstanta
sSht = "SPKL"
lRecPerPage = 30
lTotalPage = w1 /
lRecPerPage
'loop create
page
For W = 0 To w1 - 1 Step
lRecPerPage
'If W Mod 30 = 0 Or w1 = 1
Then
'ThisWorkbook.Sheets("SPKL").Copy
Before:=WAdd.Sheets(1)
hal = hal +
1
'init page number
'With
WAdd
'drop existing sheet
target
'On Error Resume
Next
'If
Not .Sheets(sSht & hal) Is Nothing
Then
' Application.DisplayAlerts =
False
' .Sheets(sSht &
hal).Delete
' Application.DisplayAlerts =
True
'End If
'Err.Clear
'On Error GoTo 0
'create
new sheet
target
ThisWorkbook.Sheets(sSht).Copy
before:=WAdd.Sheets(1)
Set SAdd =
ActiveSheet
SAdd.Name = sSht & hal
'End
With
'write page main
fields
With
SAdd
.Range("i5") =
cmb_area.Value
.Range("i6") =
txt_atasan.Value
.Range("C6") =
lbl_tgl.Caption
.Range("C41") =
w1
.Range("i60") = hal & " Dari " &
lTotalPage
End
With
'init current page record
count
If W + lRecPerPage > w1
Then
aw
= w1 - W
Else
aw
= lRecPerPage
End
If
'w1 = w1 +
1
'hal = hal +
1
'End If
'write data
record
If aw > 0
Then
With
SAdd.Range("A10").Resize(aw)
'nomor
urut
.Formula =
"=row()-9"
.Calculate
.Value =
.Value
.Offset(0, 2).Value = Format(txt_scan.Value,
"'000000")
.Offset(0, 5).Value = Left(txt_jam,
5)
.Offset(0, 6).Value = Right(txt_jam,
5)
End
With
End
If
'aw = aw +
1
Next
End Sub
Kid.
On Mon, Dec 5, 2011 at 15:13, Kid Mr. <[email protected]> wrote:
Private Sub Cmb_Generate_Click()
Dim Rng
As Range
Dim sSht As
String
Dim W As Long, w1 As Long, aw As Long, hal As
Long
Dim lRecPerPage As Long, lTotalPage As
Long
'aw = 0
'hal =
1
'init object
kerja
Set wadd = ActiveWorkbook
Set Rng = wadd.Sheets(1).Range("b2")
Set Rng =
wadd.Sheets(1).Range(Rng,
Rng.End(xlDown)) 'asumsi : tidak
mungkin terjadi record berjumlah 0
'init konstanta
sSht =
"SPKL"
lRecPerPage = 30
w1 =
Rng.Rows.Count
lTotalPage = w1 /
lRecPerPage
'loop create
page
For W = 0 To w1 - 1 Step
lRecPerPage
'If W Mod 30 = 0 Or w1 = 1 Then
'ThisWorkbook.Sheets("SPKL").Copy
Before:=WAdd.Sheets(1)
hal = hal +
1
'init page number
With
wadd
'drop existing sheet
target
On Error Resume
Next
If Not .Sheets(sSht & hal) Is Nothing
Then
Application.DisplayAlerts =
False
.Sheets(sSht &
hal).Delete
Application.DisplayAlerts =
True
End If
Err.Clear
On Error GoTo 0
'create new sheet
target
.Sheets(sSht).Copy
after:=.Sheets(1)
Set sadd =
ActiveSheet
sadd.Name = sSht & hal
End
With
'write page main
fields
With
sadd
.Range("i5") =
cmb_area.Value
.Range("i6") =
txt_atasan.Value
.Range("C6") =
lbl_tgl.Caption
.Range("C41") =
w1
.Range("i60") = hal & " Dari " &
lTotalPage
End
With
'init current page record
count
If W + lRecPerPage >
w1
Then
aw = w1 - W
Else
aw = lRecPerPage
End
If
'w1 = w1 +
1
'hal = hal +
1
'End If
'write data
record
If aw > 0
Then
With
sadd.Range("A10").Resize(aw)
'nomor
urut
.Formula =
"=row()-9"
.Calculate
.Value =
.Value
.Offset(0, 2).Value = Format(txt_scan.Value,
"'000000")
.Offset(0, 5).Value = Left(txt_jam,
5)
.Offset(0, 6).Value = Right(txt_jam,
5)
End
With
End
If
'aw = aw +
1
Next
End Sub
