Dear Mr Toby,
Sukses.....,
Ternyata masalahnya adalah di cell C1 ada karakter “/”. Soalnya a =
Sheet4.Range("c1").Value saya gunakan sebagai acuan NamaFileBaru.
Mohon koreksi untuk makro ini karena pada saat dijalankan file terSaveAs namun
sheet5 yang very hidden tidak tampil, padahal sudah sata tambahkan script
seperti warna merah dibawah ini :
Private Sub CommandButton1_Click()
Dim a As String
a = Sheet4.Range("c1").Value
On Error Resume Next
Worksheet.Active
Worksheet("Sheet5").Visible = xlVeryHidden = False
Worksheet("Sheet5").Visible = xlVisible = True
Sheets("Persetujuan Pemusnahan BS").Select
Range("B3:D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ChDir _
"D:\BAD STOCK\2015\06 Juni 2015\Persetujuan Pemusnahan BS PMA GT-MT
Nasional Juni 2015\Email Kirim ke Mr. Tian"
ActiveWorkbook.SaveAs Filename:= _
"D:\BAD STOCK\2015\06 Juni 2015\Persetujuan Pemusnahan BS PMA GT-MT
Nasional Juni 2015\Email Kirim ke Mr. Tian\" & a & ".xlsb" _
, FileFormat:=xlExcel12, CreateBackup:=False
ActiveWindow.Close
End Sub
Harapannya adalah di file yang baru sheet tersebut tampil dengan sendirinya,
mohon arahannya.
Salam,
DD
From: Toby kenzo [email protected] [belajar-excel]
[mailto:[email protected]]
Sent: 19 Juni 2015 10:22
To: [email protected]
Subject: Re: RE: [belajar-excel] Tidak bisa Locked Range
coba pake string
Sent from Yahoo Mail on Android
<http://overview.mail.yahoo.com/mobile/?.src=Android>
_____
From: Dede [email protected] [belajar-excel]
<[email protected]>;
To: <[email protected]>;
Subject: RE: [belajar-excel] Tidak bisa Locked Range
Sent: Fri, Jun 19, 2015 2:54:50 AM
Dear Mr Toby,
Sukses.....
Terima kasih atas ilmunya.
Sedikit saja saya mau bertanya, ini tentang “ Dim jmlhbaris As Integer”.
Analisa saya jmlhbaris = dalam bentuk angka
Pertanyaan saya kalau jmlhbaris = dalam bentuk text, “Dim jmlhbaris As .......?”
Saya sedang bereksperimen masalah :
Dim a As String
a = Sheet4.Range("c1").Value
Makro Sukses kalau “Sheet4 cell C1” dalam bentuk angka, tapi begitu saya rubah
“Sheet4 cell C1” dalam bentuk text makro tidak berjalan sebagaimana yang
diharapkan.
Moho arahannya.
Salam,
DD
From: Toby kenzo [email protected] [belajar-excel]
[mailto:[email protected]]
Sent: 18 Juni 2015 11:29
To: [email protected]
Subject: Bls: [belajar-excel] Tidak bisa Locked Range
Dear DD,
Coba buat commonButton dan masukan program ini:
Dim jmlhbaris As Integer
jmlhbaris = Sheet4.Range("C4").Value
ActiveSheet.Unprotect Password:="a"
Sheet4.Range(Cells(6, 3), Cells(jmlhbaris, 3)).Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
ActiveSheet.Protect Password:="a"
TerimaKasih.
Pada Kamis, 18 Juni 2015 9:37, "Dede [email protected]
[belajar-excel]" <[email protected]> menulis:
Dear Be Exceller,
Mohon bantuannya untuk koreksi makro ini :
Sub Macro4()
Jmlhbrs = Range("a1").Value
Range("a2").Select
For i = 1 To Jmlhbrs
ActiveCell.Offset(1, 0).Select
j = ActiveCell.Value
On Error Resume Next
Workbooks.Open Filename:="D:\BAD STOCK\Master\STD-STM PMA - Copy.xlsb"
ActiveSheet.Unprotect Password:="a"
Range("a6").Value = j
Range("c4").Value = s
Range("" & s & "+1,1:C100").Select
Selection.Locked = False
Selection.FormulaHidden = True
Range("c6:" & s & "").Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect Password:="a"
ChDir "D:\BAD STOCK\Email"
ActiveWorkbook.SaveAs Filename:="D:\BAD STOCK\Email\" & j & ".xlsb", _
FileFormat:=xlExcel12, CreateBackup:=False
ActiveWindow.Close
Next i
End Sub
Harapannya pada saat makro dijalankan, kolom C pada file terlampir dimana
terdapat kode barang maka range tersebut dalam keadaan ter-“Locked”.
Demikian dan terima kasih.
Salam,
DD
_____
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual to whom it is addressed. If you have
received this email in error please send it back to the person that sent it to
you. Any views or opinions presented are solely those of its author and do not
necessarily represent those of NABATI or any of its subsidiary companies.
Unauthorized publication, use, dissemination, forwarding, printing or copying
of this email and its associated attachments is strictly prohibited.
