Hai Wira,
Copy dan mem-values-kennya satu per satu saja ya (di-loop)
dim vShtName as variant
dim wbkApp as workbook,wbkTarget as workbook
dim sht as worksheet, lShtNew as long
on error resume next
lshtnew=application.sheetsinnewworkbook
application.sheetsinnewworkbook=1
application.displayalerts=false
wbkapp=thisworkbook
wbktarget=workbooks.add
for each vshtname in array("IDR", "USD", "JPY")
set sht=wbkapp.sheets(vshtname)
if err.number<>0 then
err.clear
else
sht.copy after:=wbktarget.sheets(wbktarget.sheets.count)
endif
next vshtname
if wbktarget.sheets.count>1 then
wbktarget.sheets(1).delete
endif
application.sheetsinnewworkbook=lshtnew
application.displayalerts=true
err.clear
on error goto 0
Wassalam,
Kid.
2014-03-06 19:19 GMT+07:00 WIRAnata Kemala 鄧又銘 <[email protected]>:
>
>
> dear all,
>
> saya lagi kotak-katik untuk save selected worksheets ke new workbook.
>
> katakanlah, di workbook original ada 10 worksheets, dan saya hanya ingin
> copy selected worksheet (yakni: IDR, USD, JPY) ke workbook baru.
>
> kendala nya saya adalah bagaimana jika worksheet JPY tidak ada (kebetulan
> sedang tidak ada)?
> selalu kena error "subscript out of range".
> bagaimana cara supaya selalu harus ambil worksheet IDR, USD dan JPY jika
> worksheet terkait ada, dan hanya copy - save worksheet yg ada ke workbook
> baru (jika JPY worksheet tidak ada, berarti hanya copy worksheet IDR dan
> USD lalu ke workbook baru).
>
> sepertinya harus pake catch error or something? :)
>
> berikut adalah vba simple nya :
>
> Dim ws As Worksheet
> Sheets(Array("IDR", "USD", "JPY")).Copy
> For Each ws In ActiveWorkbook.Worksheets
> With ws.UsedRange
> .Value = .Formula
> End With
> Next ws
>
> mohon konsul & bantuannya :)
>
> terima kasih banyak..
> --
> regards, Wira 鄧又銘
> on desktop FireFox
>
>
>
