CODEWITHSUNDEEP
vbaexcel
Srijith Ramachandran
Srijith Ramachandran

Reputation: 71

Range not working across sheets

I have the below code in VBA. The issue with the below code is range is referring to data sheet and as soon as I am in some other sheet it fails.

monthname = Range("D5", Range("D5").End(xlToRight)).Cells.Count - 1

Can you please provide me a solution wherein no matter where I am in the workbook range should always pick from the data sheet?

Upvotes: 0

Views: 79

Answers (3)

Srijith Ramachandran
Srijith Ramachandran

Reputation: 71

use

monthname = sheets("data").Range("D5", sheets("data").Range("D5").End(xlToRight)).Cells.Count - 1 btw a small search on this forum or google would have got you this answer.

Upvotes: 0

99moorem
99moorem

Reputation: 1983

use 

monthname = sheets("data").Range("D5", sheets("data").Range("D5").End(xlToRight)).Cells.Count - 1

btw a small search on this forum or google would have got you this answer.

Upvotes: 1

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

You are better off fully qualifying your ranges. Use

Dim ws as Worksheet
Set ws = ActiveWorkbook.Sheets("data")   ' or an alternative suitable for your case
Dim monthname as Long
monthname = ws.Range("D5", ws.Range("D5").End(xlToRight)).Cells.Count - 1

Note that the second reference to D5 is also qualified, otherwise you may find troubles.

PS: Why would you have a month name as an integer?

Upvotes: 0

Related Questions