[belajar-excel] Re: merevisi isi data

[sudarsono]

Sub zya()
Dim filerev As String
Dim sel, ketemu As Range
Dim wk, wkrev As Workbook



Set wk = ActiveWorkbook

'tetap diperlukan
sheettujuan = Sheets("kerja").Range("c2") 'ubah

' baru
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
    With fd
        .InitialView = msoFileDialogViewDetails
        .AllowMultiSelect = False
        .Filters.Add "excel", "*.xls;*.xlsx", 1
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                filerev = vrtSelectedItem
            Next vrtSelectedItem
        Else
            GoTo labelexit
        End If
    End With
    Set fd = Nothing

'   akhir baru
Set wkrev = Workbooks.Open(Filename:=filerev)

For Each sel In wkrev.Sheets(sheettujuan).Range("Q31:Q34")
nilaicari = sel.Offset(0, 0)
nilaiganti = sel.Offset(0, -1)

Set ketemu = carinilai(wk.Sheets("Sumeri").Range("N8:O29"), nilaicari)
If Not ketemu Is Nothing Then
ketemu.Offset(0, -1) = nilaiganti
End If
Next

Windows(wk.Name).Activate
labelexit:
End Sub



--- In belajar-excel@yahoogroups.com, R T Gultom <ro_tag@...> wrote:
>
> wah segampang itu merubahnya knapa ya?
> kalau ane mentok mulu...u
>
> btw, maunya lebih praktis lagi yaitu dengan cara ada open file dari folder 
> "rev.xls" jadi tidak usah menulis nama file
>
> misalnya open file select folder then select sehingga waktu dibuka itu 
> otomatis menjadi varibale
>
> Regards,
>
>
> --- On Tue, 8/2/11, jkssbma <jkssbma@...> wrote:
>
> From: jkssbma <jkssbma@...>
> Subject: RE: [belajar-excel] Re: merevisi isi data
> To: belajar-excel@yahoogroups.com
> Date: Tuesday, August 2, 2011, 3:14 PM
>
>       Adapun nama workbook dan sheet di tentukan oleh sel di B2 dan C2   
>  Sub ya()Dim filerev As StringDim sel, ketemu As RangeDim wk, wkrev As 
> Workbook      Set wk = ActiveWorkbook  filexls = 
> Sheets("kerja").Range("B2")  'rev.xlssheettujuan = 
> Sheets("kerja").Range("c2") 'ubah  filerev = ActiveWorkbook.Path & "\" & 
> filexlsSet wkrev = Workbooks.Open(Filename:=filerev)  For Each sel In 
> wkrev.Sheets(sheettujuan).Range("Q31:Q34")nilaicari = sel.Offset(0, 
> 0)nilaiganti = sel.Offset(0, -1)  Set ketemu = 
> carinilai(wk.Sheets("Sumeri").Range("N8:O29"), nilaicari)If Not ketemu Is 
> Nothing Thenketemu.Offset(0, -1) = nilaigantiEnd IfNext 
>  Windows(wk.Name).ActivateEnd sub      From: 
> belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On 
> Behalf Of R T Gultom
> Sent: Tuesday, August 02, 2011 7:54 AM
> To: belajar-excel@yahoogroups.com
> Subject: RE: [belajar-excel] Re: merevisi isi data    siap boss, ditunggu 
> karena sudah coba2, masih mentok euh
>
> --- On Mon, 8/1/11, Sudarsono Suhenk <jkssbma@...> wrote:
> From: Sudarsono Suhenk <jkssbma@...>
> Subject: RE: [belajar-excel] Re: merevisi isi data
> To: "belajar excel yahoo.com" <belajar-excel@yahoogroups.com>
> Date: Monday, August 1, 2011, 4:32 PM  Kalau file List.xls sbg file utama , 
> maka nama Rev.xls dan worksheet Revised diambil dari sel di worksheet kerja 
> , misal worksheet "kerja" di List. sel itu dapat diubah sesuai nama file 
> xls dan sel lain untuk mengubah nama worksheetnya
> Besok di sambung karena mau pulang ....