capture data cross tab

[Smithgall]

a year ago i built an invioce worksheet that captured all the data and calculated the proper prices. I built it so that every job I would simple copy the "template" sheet to a new tabe and label that tab with the invoice date. It worked rather well. HOWEVER, now i have 100+ invoices(tabs) and i have changed to a new design where i use dropdowns to populate the client and services provided.

my problem: I now have 100+ client names, address, phone#, etc that I would like to add to my table that the dropdown uses. How can i get the client name, which is in A1 for example in tab1,tab2,tab3 etc. into one worksheet where it is usable so that I would end up with a collumn that has clientname1, clientname2, clientname3 etc..

I probably should have built this is access but since i am completely ignorant of access excel has served it purpose.

 

[big_mac]

I am surprised that no one has responded. This is some VBA that I consolidated some similar information with.
Step into it so you can see how it works for you.

Sub copythestuff()
Set NewSheet = Sheets.Add(Sheet1, , 1, xlWorksheet)
other = ActiveSheet.Name
first = other
Sheets(first).Range("a1").Value = ("name")
Sheets(first).Range("B1").Value = ("address")
Sheets(first).Range("c1").Value = ("address2")
Sheets(first).Range("d1").Value = ("phone")
ctrow = 2
Worksheets(other).Activate
For Each ws In Worksheets
other = ws.Name
Name = Worksheets(other).Range("a1").Value
Address = Worksheets(other).Range("a2").Value
Address2 = Worksheets(other).Range("a3").Value
phone = Worksheets(other).Range("a4").Value
Worksheets(first).Range("a" & ctrow) = Name
Worksheets(first).Range("b" & ctrow) = Address
Worksheets(first).Range("c" & ctrow) = Address2
Worksheets(first).Range("d" & ctrow) = phone
ctrow = ctrow + 1
Next ws
Worksheets(first).Activate
End Sub

 

[Excelenator]

One more way to skin the cat.

Sub Test()
    Dim i As Long
    Dim a() As String
    
    ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
    Sheets(1).Name = "Main"
    ReDim Preserve a(Worksheets.Count - 2, 4)
    For i = 0 To Worksheets.Count - 2
        a(i, 0) = Worksheets(i + 2).Range("A1").Value
        a(i, 1) = Worksheets(i + 2).Range("B1").Value
        a(i, 2) = Worksheets(i + 2).Range("C1").Value
        a(i, 3) = Worksheets(i + 2).Range("D1").Value
        a(i, 4) = Worksheets(i + 2).Range("E1").Value
    Next i
    
    For i = 0 To UBound(a, 1)
        Worksheets("MAIN").Cells(i + 1, 1).Value = a(i, 0)
        Worksheets("MAIN").Cells(i + 1, 2).Value = a(i, 1)
        Worksheets("MAIN").Cells(i + 1, 3).Value = a(i, 2)
        Worksheets("MAIN").Cells(i + 1, 4).Value = a(i, 3)
        Worksheets("MAIN").Cells(i + 1, 5).Value = a(i, 4)
    Next i
        
End Sub

 

[Smithgall]

Thanks both of you

That worked perfectly. i knew there was a way but i couldnt figure it. What took me 1/2 a day to think about and finally ask took about 3 minutes to accomplish with your code. thanks again