Selecting all data in other worksheet VBA

Question

I am new to VBA and I am trying to write some code that selects gets the range of all of the cells in a different worksheet than the current one, prints it in a message box (for testing purposes), and returns to the original sheet. I intend to use this range to create a pivot table in the new sheet, and so In the end it needs to be of the form "R1C1:R929C25" To do this, I used code that I found here:

How do you select the entire Excel sheet with Range using Macro in VBA?

This is the code that I have so far:

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

However, this returns an error:

Run-time error '1004':
Select method of Range class failed.

Edit: I have gotten the code to get the range, yet how do I make it into the form "R1C1:R929C25"?

COMPLETED WORKING CODE:

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

score 1 · Accepted Answer · answered May 14 '15 at 14:22

1

You have two options. First, you can use a variable to store the range upon which you hope to act. If it's a one time procedure, variables aren't necessary, but if it's a repetitive task, or involves multiple references, use a variable.

Option two is to refer directly to the other range without a variable. Again, use this if it's only a one time action, such as showing a messagebox.

First example:

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

Second example:

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

One thing to note, the Sheet.Cells property returns all of the cells on the applicable sheet. So it would always be $A$1:$XFD$1048576

If you only want cells that are actually storing data, try using the UsedRange property, such as:

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

answered May 14 '15 at 14:22

basodre

5,720
1
15
23

Hello, this worked! However, since I am making a pivot table with this range, how do I change it to the form "R1C1:R929C25"? – Paradox May 14 '15 at 14:41
You don't need to convert it, nor do you need to access the address property to load it into a pivot table. Instead, refer to the variable directly: `ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, ... ` – basodre May 14 '15 at 14:53
How would I specify that range for the other sheet though? – Paradox May 14 '15 at 14:58
Do it through the code, as discussed in my original response: `Dim rng as Range` then, `Set rng = Sheets("Data_Sheet").UsedRange` then, for the pivot: `ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, etc` – basodre May 14 '15 at 15:00
What I mean is how would I create the pivot table in a different sheet, using the range from My_Sheet? – Paradox May 14 '15 at 15:04
If I directly input rng, I get a type mismatch error – Paradox May 14 '15 at 15:11
@Paradox I'm adding a new answer because it's becoming difficult to answer in comments. – basodre May 14 '15 at 15:18

score 1 · Answer 2 · answered May 14 '15 at 15:20

Per the updates and the changes in the comments, I'm including the full set of code to declare a range, and use that range in a Pivot Table to be used in a new sheet. Please note, I didn't perform full error checking, so that should be added to the code. For example, check if the new sheet name already exists, or if the pivot table name already exists, and if so, handle accordingly.

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

Davesexcel · Answer 3 · 2015-05-14T14:28:54.870

You can't select cells from a sheet you are not actively on, but you can reference them. What do you want to do with the cells in "My_Sheet_Name"

If you want the range address from a different sheet and don't know the last row or last column then you can use this code.

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

I intend to use the cells in "My_Sheet_Name" to make a pivot table in a different sheet. — Paradox, May 14 '15 at 14:41

Selecting all data in other worksheet VBA

3 Answers3