automatically getting data from another worksheet?

[oscar rodriguez]

Good morning, this is my question, I have a sheet named "DATA" with TEXT on column A, need to make a macro that goes into the sheet name after that value on A3,A4... get the last values on B:B and C:C on bring them on column B on the data sheet and do this till cell (on DATA sheet) is empty? hope i'm clear... i added an example. thanks!

 

[Hui]

Oscar

Firstly, Welcome to the Chandoo.org Forums

Copy the following code into a Code Module

Then execute it using Alt+F8

Sub Transfer_Data()

Worksheets("Data").Activate

Dim lrData As Integer, lrInfo As Integer

lrData = Range("A" & Rows.Count).End(xlUp).Row

Dim c As Range
For Each c In Range("A3:A" & lrData)
  lrInfo = Worksheets(c.Text).Range("A" & Rows.Count).End(xlUp).Row + 1
  Worksheets(c.Text).Range("A" & lrInfo).Value = c.Offset(0, 1)
  Worksheets(c.Text).Range("B" & lrInfo).Value = c.Offset(0, 2)
Next

'Uncomment next line to Clear Data range
'Range("A3:A" & lrData).ClearContents

End Sub