CODEWITHSUNDEEP
vbaexcelexcel-2010
Paradox
Paradox

Reputation: 4556

Selecting all data in other worksheet VBA

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

Upvotes: 0

Views: 3107

Answers (3)

basodre
basodre

Reputation: 5770

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

Upvotes: 1

Davesexcel
Davesexcel

Reputation: 6984

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

Upvotes: 0

basodre
basodre

Reputation: 5770

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)

Upvotes: 1

Related Questions