VBA code query: selecting ranges in another worksheet

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,875
Office Version
  1. 365
Platform
  1. Windows
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.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
Thanks Jon, can use that to reduce a few lines of code now!
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,875
Office Version
  1. 365
Platform
  1. Windows
We rarely need to select sheets / ranges. Perhaps post some of your code so we can suggest how to avoid selecting / activating...
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
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
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,875
Office Version
  1. 365
Platform
  1. Windows
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,030
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
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
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,170,963
Messages
5,873,014
Members
432,955
Latest member
minhnhat2504

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
Top