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.
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?
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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".
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
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.
Let me know if it works.
Kyle
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
Let me know if it works.
Kyle
@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?
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?
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.
Let me know if you have other questions.
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
'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
Let me know if you have other questions.
Kyle
to answer your second question...
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
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
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
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
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?
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?
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial