• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create list from multiple worksheets

maku

New Member
I've searched the forums and can't find the solution to my problem, though it may be there in a combination of multiple problems.


I have multiple worksheets in my workbook. Some of the worksheets are summary pages, data parameter pages with helper cells (etc), but most worksheets have the worksheet name derived from the property name it represents.


I need to find the best way to search every worksheet and create a list of all the properties and the location/region the property is in. (ie, Towering Heights is in New York state).


Each property worksheet has static data with labels in Column A and the data in Column B. (ie- A1=Property Name B1=Towering Heights A2=Location B2=New York).


I tried to make a pivot table with Multiple ranges selecting every property sheet name individually with cell ranges A1:B2, but I couldn't get it to "sort" by the states and then the Property names within that state.


I don't know visual basic, but looked at this page http://www.rondebruin.nl/tips.htm and think perhaps there is something there that could search each worksheet and pull those two cells B1 and B2 into a summary page with all the properties. I could then sort that list by the location the properties are in.


Is there a way to search every worksheet and if the worksheet name itself matches cell A2 then consolidate a list of properties based off the location?


If my question isn't clear, I apologize and I will try to clarify more.


Thank you for any direction.

-Maku


This post by Luke http://chandoo.org/wp/2011/11/18/formula-forensics-003/ is very close to what I need to do, but is within a single page. I don't know how to use that to look in every worksheet. If I could, then I could tell it to look in every worksheet, and if Cell A2 has "Location" then list the location.
 
I think this macro will work for you, to at least generate the list.

[pre]
Code:
Sub CreateList()
Dim sh As Worksheet
Dim FValue As String
Dim SumSheet As String

'What is the name of summary sheet?
SumSheet = "Summary"
'Which row in Summary sheet are we starting on?
i = 2
'Which column in summary sheet?
x = "A"

FValue = "Location"

With Worksheets(SumSheet)
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> SumSheet Then
If ws.Range("A2") = FValue Then
.Cells(i, x) = ws.Range("B2").Value
.Cells(i, x).Offset(0, 1) = ws.Range("B1").Value
i = i + 1
End If
End If
Next ws
End With
End Sub
[/pre]
 
Worked beautifully Luke...I would have never been able to do that by myself...I need to teach myself VB..although I can follow along your code very easily.


Thank you for your assistance, I appreciate it! You are a ninja! -Maku
 
Glad I could help. I'm a self-taught VB user myself, and would definitely encourage you to take a stab at writing some simple macros to start off with. Plenty of examples online as well. =)
 
Back
Top