Avatar of RichardPhippen
RichardPhippen
Flag for United States of America 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?
Microsoft ExcelVB Script

Avatar of undefined
Last Comment
kgerb

8/22/2022 - Mon
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
kgerb

Glad you go it working.
Kyle