VBA code query: selecting ranges in another worksheet

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
Just been reading another posting and prompted a question of my own. When I'm switiching from worksheet to worksheet and then a specific range, I find I have to write the code as:
Code:
Worksheets("Sheet1").Select
Range("A1").Select

And so can't understand why this doesn't work:
Code:
Worksheets("Sheet1").Range("A1").Select

If another sheet is currently active (say Sheet2)

Can anyone share their opinion on this or suggest a better way (if any) to code this?

Thanks,
Jack
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You would need to use:
Code:
Application.Goto Sheets("Sheet1").Range("A1")
You can't select / activate a cell on a sheet if it isn't already the active sheet, unless you use Goto.
 
Upvote 0
Thanks Jon, can use that to reduce a few lines of code now!
 
Upvote 0
We rarely need to select sheets / ranges. Perhaps post some of your code so we can suggest how to avoid selecting / activating...
 
Upvote 0
Thanks Jon,

This is just part of the code from one module - I have a few modules that are called from one main module:

Code:
Sub UpdateActualHistorical()
Dim i As Long
Dim localPrevBusiDate
Worksheets("Summary").Select
Range("A1").Select
Cells.Find(What:="T-1:", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate
localPrevBusiDate = ActiveCell.Offset(0, 1)
Application.Goto Sheets("Actual Data Historical").Range("B:B")
'Worksheets("Actual Data Historical").Select
'Range("B:B").Select
Selection.Find(What:=localPrevBusiDate, After:=ActiveCell, LookIn _
    :=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlDown, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Row
 
Upvote 0
Notice how we can run the Find method on a range within a different sheet, with not need to activate or select:

Code:
[COLOR="Blue"]Dim[/COLOR] localPrevBusiDate
[COLOR="Green"]'Worksheets("Summary").Select[/COLOR]
[COLOR="Green"]'Range("A1").Select[/COLOR]
localPrevBusiDate = Sheets("Summary").Cells.Find( _
                        What:="T-1:", _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=[COLOR="Blue"]True[/COLOR]).Offset(, 1).Value
 
Upvote 0
Just note you should also make sure you found something before you try and use the result:
Code:
Dim localPrevBusiDate
Dim rngFound as Range
set rngFound = Sheets("Summary").Cells.Find( _
                        What:="T-1:", _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=True).Offset(, 1)
If not rngFound is nothing then
   localPrevBusiDate = rngFound.Value
Else
   msgbox "Data not found!"
End If
 
Upvote 0
Thanks Rory, in this instance, that particular item will always exist, yet there is a place later where I check for a range/value found and your suggestion looks more efficient than what I have so will make use of it!
Best,
Jack
 
Upvote 0
Hi guys, hope it's ok to add this to this thread as it's a continuation of the earlier stuff. My module now looks like this, bits in red I'm not sure how to reduce - any suggestions please? And of course if you see anything else:

Rich (BB code):
Sub UpdateBrokerageHistorical()
Dim i As Long
Dim localPrevBusiDate As Date
localPrevBusiDate = Sheets("Summary").Cells.Find(What:="T-1:", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Offset(0, 1).Value
Application.Goto Sheets("Brokerage Historical").Range("B:B")
Selection.Find(What:=localPrevBusiDate, After:=ActiveCell, LookIn _
    :=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlDown, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Row
ActiveCell.Offset(0, 2).Formula = "=SUMIF('Brokerage'!$P:$P,D1,'Brokerage'!$O:$O)"
ActiveCell.Offset(1, 2).Formula = "=SUMIF('Brokerage'!$P:$P,D1,'Brokerage'!$M:$M)"
ActiveCell.Offset(2, 2).Formula = "=SUMIF('Brokerage'!$P:$P,D1,'Brokerage'!$N:$N)"
Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(2, 2)).Copy
Range("D" & i & ":Z" & i + 2).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
With Selection
    .Value = .Value
End With
Range("D1").End(xlToRight).Offset(0, 1).Select
ActiveCell.EntireColumn.ClearContents
'This column is a gap between two different data sections and needs to be kept but in future may vary if columns are added
ActiveCell.End(xlToRight).Select
ActiveCell.End(xlToRight).Offset(i - 1, 1).Select

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).ClearContents
'The two Range lines select cells to the right of the data that is not needed (but may be used in the future if column headers are added)

Cells.Columns.AutoFit
Range("A1").Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top