Excel formula or VBS

RichardPhippen
RichardPhippen used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeff DarlingDeveloper Analyst

Commented:
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 Engineer

Commented:
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 Engineer

Commented:
Here's an example workbook
28690603-01.xlsm
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

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?
Chief Engineer
Commented:
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
kgerbChief Engineer

Commented:
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

Author

Commented:
@ kgerb :

Makes total sense. Thank you very much.

Author

Commented:
Exactly what I was looking for. Perfect thanks.
kgerbChief Engineer

Commented:
You're very welcome :)

Kyle

Author

Commented:
@kgerb

Is there a way to specify that my sheets have headers and to ignore the first line?
kgerbChief Engineer

Commented:
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

Author

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 Engineer

Commented:
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

Author

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 Engineer

Commented:
If the workbook does not contain sensitive information post it and I'll take a look.

Kyle

Author

Commented:
@Kgerb

Nope, I figured it all out. Thanks for the script and info.. much appreciated.
kgerbChief Engineer

Commented:
Glad you go it working.
Kyle

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial