Macro that seeks the sheet that is filled in a cell?

[jensdevolder]

Hallo,

I'm trying to make a macro that seeks the sheet that is filled in a cell ( activecell)
and get in the sheet ,that i was seeking for, all the information that i need
( i need the information of D1-D2-D3, i need those cells filled in a general sheet in B1-B2-B3 ).

Sub Test()
Dim ws As Worksheet
Set ws = Sheets(ActiveCell.Value)
With Sheets("General") 'assuming you have a sheet named General
.Range("B1").Value = ws.Range("D1").Value
.Range("B2").Value = ws.Range("D2").Value
.Range("B3").Value = ws.Range("D3").Value
End With
End Sub

it's the intention to just use one macro like this for different names.
I mean with that, that with the next sheet name (not d1-d2-d3 but e1-e2-e3) it places the information in de cell range under the previous. ( In the same ' General' ofcours; So this is incorrrect )

Maybe this is possible with a 'loop' because all the names that are equal to a sheet are in cel A (A1-A2-A3-A4-A5-A6).

Thank you for your time

 

[Tweak155]

Hard to follow your post for me, but maybe the following?

Sub Test() 
Dim ws As Worksheet 
Dim curRow as Long
curRow = 1

While ActiveCell.Value <> ""
  Set ws = Sheets(ActiveCell.Value) 
  With Sheets("General") 'assuming you have a sheet named General
    .Cells(curRow, "B").Value = ws.Range("D1").Value 
    curRow = curRow + 1
    .Cells(curRow, "B").Value = ws.Range("D2").Value 
    curRow = curRow + 1
    .Cells(curRow, "B").Value = ws.Range("D3").Value 
    curRow = curRow + 1
  End With 

  ActiveCell.Offset(1, 0).Select
Wend
End Sub

You can now start at the first ActiveCell and list all the sheet names in order.

This will always grab values from the D column. If you have a programmatic way of knowing which column to look at based on sheet name, we can add that in.