Excel formula or VBS

I have a workbook that currently contains 10 sheets (June2015Report, May2015Report and so on). These sheets are exports from a bank, so they contain all the same information, except for the user field, which changes due to employees coming and going. My question is two fold.
I want a main sheet (MainSheet) which I am going to use to show data pulled from the other sheets.. such as total hours billed and things like that.  
The user column has multiple entries (due to different types of accounts) for each user, so Rsmith will show up 5 or 6 times.

 On the main sheet I want to pull all the users from each Report sheet and place them into say column B, but I also want to strip away any duplicates. I know how to obtain data from different sheets.. my issue is that I need to be able to have this script\macro\formula not only look at these first 10 sheets, but next month when I add July2015Report, it looks at that list of users as well.
So in short I am looking for something that will look at column B on all worksheets except for MainSheet and pull that data into column B on MainSheet and strip away duplicates.

Or am I going about this the wrong way?
LVL 1
RichardPhippenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff DarlingDeveloper AnalystCommented:
One way would be to get the list of sheets in the excel file.

Here is sample code to just list the sheets, excluding a sheet named "Summary".

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\testfile.xlsx")

   'Populate with list of current worksheets
    WS_Count = objExcel.ActiveWorkbook.Worksheets.Count
    For I = 1 To WS_Count
       If objExcel.ActiveWorkbook.Worksheets(I).Name <> "Summary" Then
         WScript.Echo objExcel.ActiveWorkbook.Worksheets(I).Name
       End If
    Next

Open in new window

kgerbChief EngineerCommented:
Hello Richard,
Try this.  It will search through all the sheets after sheet 1, pull the values from column B, and dump them in column B of MainSheet.  Then it will remove the duplicates from that newly created list of names.

Sub CopyStuff()
Dim i As Long
Dim sht As Worksheet
Dim cell1 As Range, cell2 As Range, rxfer As Range, duprng As Range
For i = 2 To Worksheets.Count
    Set sht = Worksheets(i)
    Set cell1 = IIf(sht.Range("B1") <> "", sht.Range("B1"), sht.Range("B1").End(xlDown))
    Set cell2 = sht.Cells(Rows.Count, 2).End(xlUp)
    Set rxfer = Range(cell1, cell2)
    Sheets("MainSheet").Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(rxfer.Cells.Count) = rxfer.Value
Next i
With Sheets("MainSheet")
    Set duprng = .Range(.Cells(Rows.Count, 2).End(xlUp).End(xlUp).Offset(1), .Cells(Rows.Count, 2).End(xlUp))
    .Range(duprng.Address).RemoveDuplicates Columns:=1, Header:=xlNo
End With
End Sub

Open in new window


Let me know if it works.
Kyle
kgerbChief EngineerCommented:
Here's an example workbook
28690603-01.xlsm
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Start Today!
RichardPhippenAuthor Commented:
@kgerb : Most excellent.. couple quick questions..
If the data was not in column B.. Would I just change that in the "Set Cell1 = IIf....."  line?
What do you mean "It will search through all the sheets after sheet 1"? There are no sheets called "Sheet 1", or do you mean the 1st sheet in the book, or do you mean because your example had MainSheet then Sheet2-3-4 and so on?
kgerbChief EngineerCommented:
Here's a modified version of the code that allows you to specify which column to pull the data from and which column to dump it into on MainSheet.  Just change the variables of DataCol and DataColMain.

Sub CopyStuff()
Dim i As Long
Dim sht As Worksheet
Dim cell1 As Range, cell2 As Range, rxfer As Range, duprng As Range
Dim DataCol As Long, DataColMain As Long

'DataCol is the column from which you are pulling data
'Column A = 1, B = 2, etc
DataCol = 2
'DataColMain is the column into which you are dumping data on MainSheet
DataColMain = 2

For i = 2 To Worksheets.Count
    Set sht = Worksheets(i)
    Set cell1 = IIf(sht.Cells(1, DataCol) <> "", sht.Cells(1, DataCol), sht.Cells(1, DataCol).End(xlDown))
    Set cell2 = sht.Cells(Rows.Count, DataCol).End(xlUp)
    Set rxfer = Range(cell1, cell2)
    Sheets("MainSheet").Cells(Rows.Count, DataColMain).End(xlUp).Offset(1).Resize(rxfer.Cells.Count) = rxfer.Value
Next i
With Sheets("MainSheet")
    Set duprng = .Range(.Cells(Rows.Count, DataColMain).End(xlUp).End(xlUp).Offset(1), .Cells(Rows.Count, DataColMain).End(xlUp))
    .Range(duprng.Address).RemoveDuplicates Columns:=1, Header:=xlNo
End With
End Sub

Open in new window


Let me know if you have other questions.
Kyle

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgerbChief EngineerCommented:
to answer your second question...

What do you mean "It will search through all the sheets after sheet 1"? There are no sheets called "Sheet 1", or do you mean the 1st sheet in the book, or do you mean because your example had MainSheet then Sheet2-3-4 and so on?

The code will begin its search on the second worksheet in the book and continue until the last worksheet.  It doesn't care what the sheet names are.  It's just using the numbers like 1<ignore>, 2, 3, 4, 5, ... all the way to the end.

Make sense?

Kyle
RichardPhippenAuthor Commented:
@ kgerb :

Makes total sense. Thank you very much.
RichardPhippenAuthor Commented:
Exactly what I was looking for. Perfect thanks.
kgerbChief EngineerCommented:
You're very welcome :)

Kyle
RichardPhippenAuthor Commented:
@kgerb

Is there a way to specify that my sheets have headers and to ignore the first line?
kgerbChief EngineerCommented:
sure, here you go.  This only applies to headers on the data pages.  It assumes you have a header on the MainSheet.  If this is not the case we can change that too.

Kyle

Sub CopyStuff()
Dim i As Long
Dim sht As Worksheet
Dim cell1 As Range, cell2 As Range, rxfer As Range, duprng As Range
Dim DataCol As Long, DataColMain As Long
Dim HasHeaders As Boolean

'DataCol is the column from which you are pulling data
'Column A = 1, B = 2, etc
DataCol = 2
'DataColMain is the column into which you are dumping data on MainSheet
DataColMain = 2

'Set to true if your data has headers
HasHeaders = False

For i = 2 To Worksheets.Count
    Set sht = Worksheets(i)
    Set cell1 = IIf(sht.Cells(1, DataCol) <> "", sht.Cells(1, DataCol), sht.Cells(1, DataCol).End(xlDown))
    If HasHeaders Then Set cell1 = cell1.Offset(1)
    Set cell2 = sht.Cells(Rows.Count, DataCol).End(xlUp)
    Set rxfer = Range(cell1, cell2)
    Sheets("MainSheet").Cells(Rows.Count, DataColMain).End(xlUp).Offset(1).Resize(rxfer.Cells.Count) = rxfer.Value
Next i
With Sheets("MainSheet")
    Set duprng = .Range(.Cells(Rows.Count, DataColMain).End(xlUp).End(xlUp).Offset(1), .Cells(Rows.Count, DataColMain).End(xlUp))
    .Range(duprng.Address).RemoveDuplicates Columns:=1, Header:=xlNo
End With
End Sub

Open in new window

28690603-02.xlsm
RichardPhippenAuthor Commented:
thanks again.. Only issue is that the first name (alphabetically) on the imported list is now being duplicated.

I changed it back to the 2nd script you sent.. and it pulls the header and does duplicate the first name..
Placed in the new code, (changed to my mainsheet name and "false" to "true" and this does remove the header from the data pages, but the first name appears twice.

One thing to mention, the data sheets are formatted as tables.. is this an issue?
kgerbChief EngineerCommented:
hmmm...Not sure why it's not removing all the duplicates.  It's working fine in my example.  I changed all the lists in the data sheets to tables and it still works (as well it should).

Are you sure the names are EXACTLY the same and you don't have an "Aaron Smith" and an "Aaron Smith " (note the extra space).

Kyle
28690603-03.xlsm
RichardPhippenAuthor Commented:
I will double check everything.. but since your first code did not duplicate this (or any) user.. It might be something strange within the worksheets.. I will start a new workbook and play with it there.

update tomorrow.
kgerbChief EngineerCommented:
If the workbook does not contain sensitive information post it and I'll take a look.

Kyle
RichardPhippenAuthor Commented:
@Kgerb

Nope, I figured it all out. Thanks for the script and info.. much appreciated.
kgerbChief EngineerCommented:
Glad you go it working.
Kyle
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.