Memilih semua data di lembar kerja VBA lainnya

Saya baru mengenal VBA dan saya mencoba menulis beberapa kode yang memilih mendapatkan rentang semua sel di lembar kerja yang berbeda dari yang sekarang, mencetaknya di kotak pesan (untuk tujuan pengujian), dan kembali ke lembar asli . Saya bermaksud menggunakan rentang ini untuk membuat tabel pivot di lembar baru, sehingga pada akhirnya harus berbentuk "R1C1:R929C25" Untuk melakukan ini, saya menggunakan kode yang saya temukan di sini:

Bagaimana Anda memilih seluruh lembar Excel dengan Range menggunakan Makro di VBA?

Ini adalah kode yang saya miliki sejauh ini:

Sheets("My_Sheet_Name").Cells.Select
MsgBox (Str(Range(Cells.Address)))
Sheets(Sheets.Count).Select

Namun, ini mengembalikan kesalahan:

Kesalahan run-time '1004':
Pemilihan metode kelas Range gagal.

Sunting: Saya sudah mendapatkan kode untuk mendapatkan rangenya, namun bagaimana cara membuatnya menjadi bentuk "R1C1:R929C25"?

KODE KERJA YANG LENGKAP:

Dim rng As Range
Set rng = Sheets("My_Sheet").UsedRange
Dim rc_range
rc_range = "R" & Trim(Str(rng.Rows.Count)) & "C" &       Trim(Str(rng.Columns.Count))
MsgBox (rc_range)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"My_Sheet!R1C1:" & rc_range, Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:=ActiveSheet.Name + "!R4C4",

TableName:=inputValue + "_PivotTable" _ , Versi Default:=xlPivotTableVersion10


excel vba excel-2010
person Paradox    schedule 14.05.2015    source sumber

Jawaban (3)


arrow_upward
1
arrow_downward

Anda memiliki dua pilihan. Pertama, Anda dapat menggunakan variabel untuk menyimpan rentang yang ingin Anda gunakan. Jika prosedurnya satu kali, variabel tidak diperlukan, tetapi jika tugas tersebut berulang, atau melibatkan banyak referensi, gunakan variabel.

Opsi kedua adalah merujuk langsung ke rentang lainnya tanpa variabel. Sekali lagi, gunakan ini jika hanya tindakan satu kali, seperti menampilkan kotak pesan.

Contoh pertama:

Set rng = Sheets("My_Sheet").Cells
msgbox(rng.address)

Contoh kedua:

msgbox(Sheets("My_Sheet").Cells.Address)

Satu hal yang perlu diperhatikan, properti Sheet.Cells mengembalikan semua sel pada lembar yang berlaku. Jadi akan selalu $A$1:$XFD$1048576

Jika Anda hanya menginginkan sel yang benar-benar menyimpan data, coba gunakan properti UsedRange, seperti:

Set rng = Sheets("My_Sheet").UsedRange
msgbox(rng.address)
person basodre    schedule 14.05.2015
comment
Halo, ini berhasil! Namun karena saya membuat tabel pivot dengan range ini, bagaimana cara mengubahnya ke bentuk R1C1:R929C25? - person Paradox; 14.05.2015
comment
Anda tidak perlu mengonversinya, Anda juga tidak perlu mengakses properti alamat untuk memuatnya ke dalam tabel pivot. Sebagai gantinya, lihat variabel secara langsung: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, ... - person basodre; 14.05.2015
comment
Bagaimana cara menentukan rentang itu untuk lembar lainnya? - person Paradox; 14.05.2015
comment
Lakukan melalui kode, seperti yang dibahas dalam respons awal saya: Dim rng as Range lalu, Set rng = Sheets("Data_Sheet").UsedRange lalu, untuk pivot: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, etc - person basodre; 14.05.2015
comment
Maksud saya adalah bagaimana cara membuat tabel pivot di lembar berbeda, menggunakan rentang dari Lembar_Saya? - person Paradox; 14.05.2015
comment
Jika saya langsung memasukkan rng, saya mendapatkan kesalahan tipe ketidakcocokan - person Paradox; 14.05.2015
comment
@Paradox Saya menambahkan jawaban baru karena semakin sulit dijawab di komentar. - person basodre; 14.05.2015

arrow_upward
1
arrow_downward

Berdasarkan pembaruan dan perubahan di komentar, saya menyertakan set kode lengkap untuk mendeklarasikan suatu rentang, dan menggunakan rentang tersebut dalam Pivot Table untuk digunakan di lembar baru. Harap dicatat, saya tidak melakukan pemeriksaan kesalahan penuh, jadi itu harus ditambahkan ke kode. Misalnya, periksa apakah nama sheet baru sudah ada, atau apakah nama tabel pivot sudah ada, dan jika ya, tangani sesuai dengan itu.

Sub CreatePivotFromDynamicRange()
    Dim wsNew As Worksheet
    Dim rngData As Range
    Dim rngDestination As Range

    'Add a new worksheet to store the pivot table
    '[NOTE] if sheet named "PivotSheet" already exists it will throw error
    Set wsNew = Worksheets.Add()
    wsNew.Name = "PivotSheet"

    'Define the range with the data needed for the pivot
    Set rngData = Sheets("My_Data").UsedRange

    'define the pivot destination
    Set rngDestination = wsNew.Range("A5")

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, _
        Version:=xlPivotTableVersion14).CreatePivotTable tabledestination:=rngDestination, _
        tablename:="NewPivot", defaultVersion:=xlPivotTableVersion14


End Sub
person basodre    schedule 14.05.2015

arrow_upward
0
arrow_downward

Anda tidak dapat memilih sel dari lembar yang tidak Anda gunakan secara aktif, namun Anda dapat mereferensikannya. Apa yang ingin Anda lakukan dengan sel di "My_Sheet_Name"

Jika Anda ingin alamat rentang dari lembar lain dan tidak mengetahui baris atau kolom terakhir, Anda dapat menggunakan kode ini.

Sub SelectLastCellInInSheet()
    Dim sh As Worksheet
    Dim Rws As Long, Col As Integer, r As Range, fRng As Range

    Set sh = Sheets("My_Sheet_Name")

    With sh
        Set r = .Range("A1")
        Rws = .Cells.Find(what:="*", after:=r, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Col = .Cells.Find(what:="*", after:=r, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set fRng = .Range(.Cells(1, 1), .Cells(Rws, Col))
    End With

    MsgBox fRng.Address
End Sub
person Davesexcel    schedule 14.05.2015
comment
Saya bermaksud menggunakan sel di My_Sheet_Name untuk membuat tabel pivot di sheet berbeda. - person Paradox; 14.05.2015