[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Referring to a specific cell on a sheet other than the current

There are some tasks I want to perform on each sheet in a workbook and I'd like to do this without Select and/or Activate statements.  I utilize a cell on each sheet as my "home base" and then offset from there, however, when I try to assign a variable name to a cell on a sheet other than the current one, I get a 1004 error.  My code is as follows:
Set HomeSpot = Sheets("SheetsNum").Range("a1") .Range("B4")

Open in new window

This is the line that errors out, even if I just do a short macro trying to select the cell on another sheet.  How can I rewrite the code to pass muster?  Thanks!
P.S. Same error if I precede the above code with "Acvtiveworkbook." and/or use Worksheets instead of Sheets .
0
pmpatane
Asked:
pmpatane
  • 3
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

remove the ".Range("B4") from that line of code. You can only have only one range in the Set statement.

cheers, teylyn
0
 
pmpataneAuthor Commented:
Sorry, that was a mis-paste.  It is Set HomeSpot = Sheets("SheetsNum").Range("B4"), and it still gives the 1004 error
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Try to qualify the workbook. This works in my tests:

Sub test()
Dim wb As Workbook
Dim ws As Worksheet
Dim HomeSpot As Range

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet3")
Set HomeSpot = ws.Range("A1")

HomeSpot.Value = "Hello World"

End Sub 

Open in new window


cheers, teylyn
0
 
pmpataneAuthor Commented:
Sorry for the delay, I have been out of the office.  Teylyn, I will check your suggestion first thing tomorrow...thanks!
0
 
pmpataneAuthor Commented:
Thanks!  Sorry for my delay...sometimes I don't get all the overhead, but I'm sure there's a reason
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now