Terima kasih Mr. Kid atas penjelasannya.
Penulisan type data inilah yang kadang2 masih selip...Saya coba seperti ini
ternyata masih error,Sub All_x_Tes() Dim sh As Worksheet Dim shNames As
Variant For Each sh In ThisWorkbook.Worksheets If sh.Name <> "TES"
Then If shNames = "" Then shNames = """""" shNames =
shNames & sh.Name & """"", """"" End If Next shNames =
Left(shNames, Len(shNames) - 4) Sheets(Array(shNames)).Select 'masih
errorEnd Sub
Mohon petunjuk lebih lanjut mengenai teknis penulisannya.Terima kasih.
Salam,HK
From: "'Mr. Kid' [email protected] [belajar-excel]"
<[email protected]>
To: BeExcel <[email protected]>
Sent: Wednesday, 18 February 2015, 9:56
Subject: Re: [belajar-excel] Memilih sheets sekaligus kecuali sheet TES
hehehe... kalo ini jelas bukan quiz, soale ada keterangan resminya...
masalahnya adalah tipe data.
Pada
Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")yang berupa string adalah nilai
Sheet1 kemudian nilai Sheet2 lalu nilai Sheet4 beserta nilai Sheet5 karena
semua nilai-nilai itu didefinisikan dengan diapit tanda petik.
Jadi, bunyi "Sheet1", "Sheet2", "Sheet4", "Sheet5" (secara utuh) bukanlah
string yang bernilai "Sheet1", "Sheet2", "Sheet4", "Sheet5"
Array(shNames)dengan shNames bertipe string, jika ditulis lugas akan menjadi :
Array("""Sheet1"", ""Sheet2"", ""Sheet4"", ""Sheet5""")
dan berbeda dengan bunyi :
Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")
bukan ?
bedanya :
Array("Sheet1", "Sheet2", "Sheet4", "Sheet5") membentuk array dengan 4 item
didalamnya
dan
Array("""Sheet1"", ""Sheet2"", ""Sheet4"", ""Sheet5""") membentuk array dengan
1 item didalamnya yang berbunyi "Sheet1", "Sheet2", "Sheet4", "Sheet5"hasilnya,
proses oleh methods Select menghasilkan error karena tidak ada sheet bernama
"Sheet1", "Sheet2", "Sheet4", "Sheet5" dalam workbook.
sekarang pakai bahasa programmer :
Array() adalah function yang bisa menerima parameter berupa array alias daftar
nilai sheet1 yang bertipe teks alias berbunyi lengkap "Sheet1" menjadi nilai
parameter ke-1 bagi fungsi Array
begitu pula untuk nilai sheet2, sheet4, dan sheet5.
Regards,
Kid.
2015-02-18 13:42 GMT+11:00 hendrik karnadi [email protected]
[belajar-excel] <[email protected]>:
Dear be-Exceler,
Beberapa waktu yang lalu pernah ada pertanyaan:"Bagaimana memilih beberapa
sheet sekaligus kecuali satu sheet tertentu dengan macro".Untuk contoh kasus
saya buat lima sheets dimana satu sheet tertentu tsb bernama "TES" sebagai
ganti Sheet3
Jika digunakan rekaman (macro recording) maka codenya seperti
ini:Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")).Select
Karena jumlah sheet bisa banyak maka saya buat macro seperti ini:Sub
All_x_Tes() Dim sh As Worksheet Dim shNames As Variant For Each sh In
ThisWorkbook.Worksheets If sh.Name <> "TES" Then shNames =
shNames & sh.Name & """, """ End If Next shNames = Left(shNames,
Len(shNames) - 4) Sheets(Array(shNames)).SelectEnd Sub
Ketika saya jalankan keluar message :Run-time error '9':Subscript out of range
Ketika saya debug maka code ini diberi warna
kuning:Sheets(Array(shNames)).Select
Jika disorot warna kuningnya maka akan tampakshNames = "Sheet1", "Sheet2",
"Sheet4", "Sheet5" sehingga kalau digabung menjadi:Sheets(Array("Sheet1",
"Sheet2", "Sheet4", "Sheet5")).Select
Pertanyaannya (bukan quiz):Dimana salahnya ?
Terima kasih.
Salam,HK
#yiv2264557507 #yiv2264557507 -- #yiv2264557507ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2264557507
#yiv2264557507ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2264557507
#yiv2264557507ygrp-mkp #yiv2264557507hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv2264557507 #yiv2264557507ygrp-mkp #yiv2264557507ads
{margin-bottom:10px;}#yiv2264557507 #yiv2264557507ygrp-mkp .yiv2264557507ad
{padding:0 0;}#yiv2264557507 #yiv2264557507ygrp-mkp .yiv2264557507ad p
{margin:0;}#yiv2264557507 #yiv2264557507ygrp-mkp .yiv2264557507ad a
{color:#0000ff;text-decoration:none;}#yiv2264557507 #yiv2264557507ygrp-sponsor
#yiv2264557507ygrp-lc {font-family:Arial;}#yiv2264557507
#yiv2264557507ygrp-sponsor #yiv2264557507ygrp-lc #yiv2264557507hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2264557507
#yiv2264557507ygrp-sponsor #yiv2264557507ygrp-lc .yiv2264557507ad
{margin-bottom:10px;padding:0 0;}#yiv2264557507 #yiv2264557507actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2264557507
#yiv2264557507activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2264557507
#yiv2264557507activity span {font-weight:700;}#yiv2264557507
#yiv2264557507activity span:first-child
{text-transform:uppercase;}#yiv2264557507 #yiv2264557507activity span a
{color:#5085b6;text-decoration:none;}#yiv2264557507 #yiv2264557507activity span
span {color:#ff7900;}#yiv2264557507 #yiv2264557507activity span
.yiv2264557507underline {text-decoration:underline;}#yiv2264557507
.yiv2264557507attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv2264557507 .yiv2264557507attach div a
{text-decoration:none;}#yiv2264557507 .yiv2264557507attach img
{border:none;padding-right:5px;}#yiv2264557507 .yiv2264557507attach label
{display:block;margin-bottom:5px;}#yiv2264557507 .yiv2264557507attach label a
{text-decoration:none;}#yiv2264557507 blockquote {margin:0 0 0
4px;}#yiv2264557507 .yiv2264557507bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv2264557507
.yiv2264557507bold a {text-decoration:none;}#yiv2264557507 dd.yiv2264557507last
p a {font-family:Verdana;font-weight:700;}#yiv2264557507 dd.yiv2264557507last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2264557507
dd.yiv2264557507last p span.yiv2264557507yshortcuts
{margin-right:0;}#yiv2264557507 div.yiv2264557507attach-table div div a
{text-decoration:none;}#yiv2264557507 div.yiv2264557507attach-table
{width:400px;}#yiv2264557507 div.yiv2264557507file-title a, #yiv2264557507
div.yiv2264557507file-title a:active, #yiv2264557507
div.yiv2264557507file-title a:hover, #yiv2264557507 div.yiv2264557507file-title
a:visited {text-decoration:none;}#yiv2264557507 div.yiv2264557507photo-title a,
#yiv2264557507 div.yiv2264557507photo-title a:active, #yiv2264557507
div.yiv2264557507photo-title a:hover, #yiv2264557507
div.yiv2264557507photo-title a:visited {text-decoration:none;}#yiv2264557507
div#yiv2264557507ygrp-mlmsg #yiv2264557507ygrp-msg p a
span.yiv2264557507yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2264557507
.yiv2264557507green {color:#628c2a;}#yiv2264557507 .yiv2264557507MsoNormal
{margin:0 0 0 0;}#yiv2264557507 o {font-size:0;}#yiv2264557507
#yiv2264557507photos div {float:left;width:72px;}#yiv2264557507
#yiv2264557507photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv2264557507
#yiv2264557507photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2264557507
#yiv2264557507reco-category {font-size:77%;}#yiv2264557507
#yiv2264557507reco-desc {font-size:77%;}#yiv2264557507 .yiv2264557507replbq
{margin:4px;}#yiv2264557507 #yiv2264557507ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv2264557507 #yiv2264557507ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2264557507
#yiv2264557507ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2264557507
#yiv2264557507ygrp-mlmsg select, #yiv2264557507 input, #yiv2264557507 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv2264557507
#yiv2264557507ygrp-mlmsg pre, #yiv2264557507 code {font:115%
monospace;}#yiv2264557507 #yiv2264557507ygrp-mlmsg *
{line-height:1.22em;}#yiv2264557507 #yiv2264557507ygrp-mlmsg #yiv2264557507logo
{padding-bottom:10px;}#yiv2264557507 #yiv2264557507ygrp-msg p a
{font-family:Verdana;}#yiv2264557507 #yiv2264557507ygrp-msg
p#yiv2264557507attach-count span {color:#1E66AE;font-weight:700;}#yiv2264557507
#yiv2264557507ygrp-reco #yiv2264557507reco-head
{color:#ff7900;font-weight:700;}#yiv2264557507 #yiv2264557507ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv2264557507 #yiv2264557507ygrp-sponsor
#yiv2264557507ov li a {font-size:130%;text-decoration:none;}#yiv2264557507
#yiv2264557507ygrp-sponsor #yiv2264557507ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv2264557507
#yiv2264557507ygrp-sponsor #yiv2264557507ov ul {margin:0;padding:0 0 0
8px;}#yiv2264557507 #yiv2264557507ygrp-text
{font-family:Georgia;}#yiv2264557507 #yiv2264557507ygrp-text p {margin:0 0 1em
0;}#yiv2264557507 #yiv2264557507ygrp-text tt {font-size:120%;}#yiv2264557507
#yiv2264557507ygrp-vital ul li:last-child {border-right:none
!important;}#yiv2264557507
