asked on

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?

Jeff Darling

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

kgerb

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

kgerb

Here's an example workbook
28690603-01.xlsm

RichardPhippen

ASKER

@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?

ASKER CERTIFIED SOLUTION

kgerb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kgerb

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

RichardPhippen

ASKER

@ kgerb :

Makes total sense. Thank you very much.

RichardPhippen

ASKER

Exactly what I was looking for. Perfect thanks.

kgerb

You're very welcome :)

Kyle

RichardPhippen

ASKER

@kgerb

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

kgerb

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

RichardPhippen

ASKER

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?

kgerb

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

RichardPhippen

ASKER

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.

kgerb

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

Kyle

RichardPhippen

ASKER

@Kgerb

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

kgerb

Glad you go it working.
Kyle