Выбор всех данных на другом листе VBA

Я новичок в VBA, и я пытаюсь написать код, который выбирает, получает диапазон всех ячеек на листе, отличном от текущего, печатает его в окне сообщения (для целей тестирования) и возвращает на исходный лист . Я намерен использовать этот диапазон для создания сводной таблицы на новом листе, поэтому в итоге он должен иметь вид «R1C1:R929C25». Для этого я использовал код, который нашел здесь:

Как вы выбираете весь лист Excel с диапазоном, используя макрос в VBA?

Это код, который у меня есть до сих пор:

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

Однако это возвращает ошибку:

Ошибка выполнения "1004":
Не удалось выбрать метод класса Range.

Редактировать: я получил код для получения диапазона, но как мне преобразовать его в форму «R1C1:R929C25»?

ЗАВЕРШЕННЫЙ РАБОЧИЙ КОД:

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" _ , DefaultVersion:=xlPivotTableVersion10


excel vba excel-2010
person Paradox    schedule 14.05.2015    source источник

Ответы (3)


arrow_upward
1
arrow_downward

У вас есть два варианта. Во-первых, вы можете использовать переменную для хранения диапазона, в котором вы хотите действовать. Если это однократная процедура, переменные не нужны, но если это повторяющаяся задача или включает несколько ссылок, используйте переменную.

Второй вариант — обратиться непосредственно к другому диапазону без переменной. Опять же, используйте это, если это однократное действие, например, отображение окна сообщения.

Первый пример:

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

Второй пример:

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

Следует отметить, что свойство Sheet.Cells возвращает все ячейки на соответствующем листе. Так что всегда будет $A$1:$XFD$1048576

Если вам нужны только ячейки, которые фактически хранят данные, попробуйте использовать свойство UsedRange, например:

Set rng = Sheets("My_Sheet").UsedRange
msgbox(rng.address)
person basodre    schedule 14.05.2015
comment
Здравствуйте, это сработало! Однако, поскольку я создаю сводную таблицу с этим диапазоном, как мне изменить его на форму R1C1: R929C25? - person Paradox; 14.05.2015
comment
Вам не нужно его преобразовывать, а также вам не нужно обращаться к свойству адреса, чтобы загрузить его в сводную таблицу. Вместо этого обратитесь к переменной напрямую: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, ... - person basodre; 14.05.2015
comment
Как бы я указал этот диапазон для другого листа? - person Paradox; 14.05.2015
comment
Сделайте это с помощью кода, как обсуждалось в моем первоначальном ответе: Dim rng as Range затем, Set rng = Sheets("Data_Sheet").UsedRange затем, для разворота: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, etc - person basodre; 14.05.2015
comment
Я имею в виду, как мне создать сводную таблицу на другом листе, используя диапазон из My_Sheet? - person Paradox; 14.05.2015
comment
Если я напрямую ввожу rng, я получаю ошибку несоответствия типа - person Paradox; 14.05.2015
comment
@Paradox Я добавляю новый ответ, потому что становится трудно отвечать в комментариях. - person basodre; 14.05.2015

arrow_upward
1
arrow_downward

В соответствии с обновлениями и изменениями в комментариях я включаю полный набор кода для объявления диапазона и использую этот диапазон в Pivot Table для использования на новом листе. Обратите внимание, я не выполнял полную проверку ошибок, поэтому это следует добавить в код. Например, проверьте, существует ли уже имя нового листа или имя сводной таблицы, и если да, обработайте его соответствующим образом.

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

Вы не можете выбирать ячейки на листе, на котором вы не находитесь активно, но вы можете ссылаться на них. Что вы хотите сделать с ячейками в "My_Sheet_Name"

Если вам нужен адрес диапазона с другого листа и вы не знаете последнюю строку или последний столбец, вы можете использовать этот код.

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
Я намерен использовать ячейки в My_Sheet_Name для создания сводной таблицы на другом листе. - person Paradox; 14.05.2015