CODEWITHSUNDEEP
vbaexcel
user3553679
user3553679

Reputation: 1

Finding a cell on one sheet, picking up data from the same cell on a different sheet

Basically, I am comparing sales figures from previous years and want a macro that when a new months data is added will compare this to the same month last year.

I have set up a macro to find the last cell with data in it to pull through the most recent months data for the current year. But is there a way I get it to select the same cell on a different sheet? I cannot use the same system of finding last line with data because on previous years all data is complete.

I hope this makes sense! Thanks!

Upvotes: 0

Views: 68

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

  1. find the cell on the first sheet
  2. get the address
  3. use that address to get a value from the second sheet

For example:

Sub SameCellDifferentSheet()
  Dim r As Range, addy As String
  Set r = Sheets("Sheet1").Cells.Find("happiness")
  addy = r.Address
  Othervalue = Sheets("Sheet2").Range(addy)
End Sub

Upvotes: 1

Related Questions