Computer Support Forum

Sum Distinct Text on multiple sheets

Question: Sum Distinct Text on multiple sheets

I have a follow up question to idea how to do this if your criteria is not YES or NO but rather you want to sum distinct text string from multiple worksheets?Sheet 1DogCatCatBirdSheet 2DogFishthe sum would be equal to 4 (Dog, Cat, Bird and Fish)I believe it would be some formula combination of SUMPRODUCT, COUNTIF and INDIRECT.

More replies
Relevance 77.9%

EXCEL SOS!!! I have an urgent deadline which I'm likely to fail if I can't get my head around this issue so please please feel free to comment / offer suggestions...I'll try anything!

I have around 10 - 15 sheets within the same excle file which is effectively acting as one tab per resource within my consultancy team. These are acting a a record of the billable & non billable hours per resource and therefore each line is primarily driven by date (2011 - 2013), with 1:N ratio items of the following: Client, Contract, Role, Days & hrs (effectively 1 line per date, resource, client & contract combination + time billed within the std 8 hr working day).

I have no problems creating individual pivot tables in the individual resource name's relevant tab, however the next step is to roll this information togather to show a holistic view for all resources, all clients etc (in what I anticipated to be a pivot table via the consolidation of multiple ranges function) for all resources by exactly the same fields:

Year, Date, Contract/Type, Resource, & Date - at row level (with possible additional options of Month, Week, Day being added as a row label)


CLIENT , Non Billable - at column level

Naturally there may well be some slicing and dicing of the info needed to understand the figures at different levels, ie per resource (days billed & rate), how many hours used per contract, how we've used time in a contract (by role), & whom (by... Read more

Answer:Solved: Pivot multiple sheets for text, date and decimal fields

Hi there, welcome to the board!

I feel your frustration. Unfortunately your data structure isn't conducive for a PivotTable data model. But there is hope! You have some options here. First of all we need to know what version you're using. If you have 2010, well, you're going to really like what I'm about to tell you, and if you don't have it, it's a good reason to upgrade! 2010 has a new add-in called PowerPivot. Think of it as PivotTable's on bulk steroids. The benefit you would see - the ability to select multiple data sources. This means you can have multiple data tables/sheets as your data source, and select their relationships. Think of it like an Access table/relationship. It's the best feature we've seen in Excel since the PivotTable itself.

So if you have 2010, this is good news. However, if you don't have 2010, we need to consolidate your data to a single data structure. Whether that is putting it all on one sheet, or exporting to Access, or whatever, that's what needs to happen.

I'm not entirely sure what you mean about not getting the row level structure. Is there any way you can post the file?

2 more replies
Relevance 75.44%

So, I've been looking up the topic for quite a while and it's suddenly become critical to what I am doing. Everywhere I have looked, I see lots of ways to copy multiple sheets into one sheet. This doen't do me the least bit of good. I have two 70+ tab spreadsheets. One for actuals, one for forecast. I need to copy the actuals for the first 5 fiscal periods of the year into the Forecast file. As there are 70+ sheets, I would prefer not to do this one at a time. In know in the previous versions of Excel, I could simply highlight all the sheets, highlight the data I needed, hit copy, and paste the data in the new sheet. So long as the sheets were laid out the same, it was never a problem.

Do you have any idea how to do this now?

Thank you.

Answer:Excel 2010 copying multiple sheets into multiple sheets

7 more replies
Relevance 69.7%

Find Function in Word XP:

Is there any way to search for a file by contained phrase?

If I enter a two-word phrase into the search parameter, I get files with either word. What I'm looking to find is just files with the two-word phrase.

You can see how frustrating this can be if I want to search by a whole sentence. I'd get results for each individual word in the sentence.

Answer:File search by distinct text phrase

Try Agent Ransack:

It will do that, it supports Regular Expression search, and it's a LOT faster than Windows Find Files.


2 more replies
Relevance 63.96%

Hello VBA experts. Need your help. I have a workbook with 14 worksheets. Each worksheet has several fields all named the same. I need to sort 3 fields in each worksheet. Those are ascending in this order; Patient ID (in column A), DOS (in column E) and Code (column B).
Does anyone know of a code that can easily sort all 14 tabs at once? The range of data in each worksheet are different. Some worksheets have very little data to sort while other worksheets have maybe a 2,000 rows of data.
Any help is greatly appreciated.

Answer:Solved: Excel 2010 - Sort multiple fields in multiple sheets


Here is a macro that will sort all spreadsheets each with 3 levels of sort, all ascending assuming that the number of rows in all columns is the same.

Give this a try on a copy of your file (can't stress this enough) and let us know what it didn't do right
[SIZE=1]Sub SortSheets()[/SIZE]
[SIZE=1][/SIZE][SIZE=1]' Macro1 Macro[/SIZE]
[SIZE=1][/SIZE][SIZE=1]For i = 1 To Sheets.Count[/SIZE]
[SIZE=1][/SIZE][SIZE=1] ActiveWorkbook.Worksheets(i).Sort.SortFields.Clear[/SIZE]
[SIZE=1][/SIZE][SIZE=1] ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row) _[/SIZE]
[SIZE=1][/SIZE][SIZE=1] , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/SIZE]
[SIZE=1][/SIZE][SIZE=1] ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row) _[/SIZE]
[SIZE=1][/SIZE][SIZE=1] , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/SIZE]
[SIZE=1][/SIZE][SIZE=1] ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row) _[/SIZE]
[SIZE=1][/SIZE][SIZE=1] , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/SIZE]
[SIZE=1][/SIZE][SIZE=1] With ActiveWorkbook.Worksheets(i).Sort[/SIZE]
[SIZE=1][/SIZE][SIZE=... Read more

2 more replies
Relevance 62.73%

I want to look up criteria over multiple sheets in Excel and return all the values of a cell that match that criteria. Similar to a Vlookup but over an undefined number sheets and to return multiple values. Can anyone help?

Answer:A Vlookup over multiple sheets and returning multiple values

This code assumes that the headings shown above begin in A1 in all sheets.Paste the code into the Sheet Module for Sheet1 and then enter a tracking number in B2.Private Sub Worksheet_Change(ByVal Target As Range)
'Check to see if change was made to B2
If Target.Address = "$B$2" Then
'If Yes, disable events
Application.EnableEvents = False
'Store Track Number
trackNum = Target
'Find last row in Sheet1
endRow = Range("B" & Rows.Count).End(xlUp).Row
'Clear Sheet1
Range("A2:I" & endRow).ClearContents
'Loop through Sheets
For shtNum = 2 To Sheets.Count
'Find last row and begin search for Track number
lastRow = Sheets(shtNum).Range("A" & Rows.Count).End(xlUp).Row
With Sheets(shtNum).Range("A1:A" & lastRow)
Set t = .Find(trackNum)
If Not t Is Nothing Then
firstAddress = t.Address
'For each Track number found, Copy data to next Row on Sheet1
nxtRow = Range("B" & Rows.Count).End(xlUp).Row + 1
Range("A" & nxtRow) = nxtRow - 1
.Range("A" & t.Row & ":D" & t.Row).Copy _
Destination:=Range("B" & nxtRow)
.Range("E" & t.Row).Copy _
Destination:=Range("G" & nxtRow)
.Range("F" & t.Row).Copy _
Destination:=Range("F" & nxtRow)
.Range("G" & t.Row & ":H" & t.Row).Copy _
Destination:=Range("H" & nxtRow)
Set t = .FindNext(t)
... Read more

14 more replies
Relevance 59.86%

I am struggling to solve the following problem.I have multi sheet spread sheet from which I am trying to add all the occurrences of a given text heading e.g. MEASHAM on two or more sheets but in the same column.I can do this for ONE sheet using; =COUNTIFS(?SHEET 1?!E:E,?MEASHAM?)But cannot resolve more than one sheet.

Answer:How can I add text headings on multi sheets

Perhaps create a specific template formatted that way? Not that "up" on Excel sheets etc., but the template approach generally works with Word so can't immediately see why not with Excel

5 more replies
Relevance 58.22%

Hi there. This has been bugging me for ages..hope someone can help.I have created a tracker that records absences at work. Each team has their own individual work sheet (11 in total). Column B contains their name and Column C contains the number of instances they have had off.The summary page, contains the name of every employee. I want to use a vlookup to search the different tabs and return the value in column BHow can I do this?appreciate any help

Answer:Vlookup over multiple sheets

Hi,The problem is that the MultVlookup function was designed to stop after finding the first match.You correctly changed the logic to allow it to loop repeatedly, i.e. not stop after finding the first match, but the Find function does not stop when it has gone through the range specified - it just starts again, so you need to add a test to show that it has got back to where it started from - in practical terms if it finds a matching cell that is the one it found first of all.Secondly, Find always starts with the cell after the first cell in the range passed to it. The accepted way to repeat Finds with each find starting after the last cell matched is to use FindNext - but this does not work when called from a UDF. As a result you have to use repeated Finds and manage the starting addresses each time.In your case because you want multiple 'finds', the code I proposed in VlookupMin is more appropriate as a starting point.You will see that this saves an address and uses it as a test in the Do Until loop, the loop stopping when the address found matches the saved address of the first matching cell.I made a few mods to the VlookupMin and this is what I came up with:Public Function MultVlookupW( _
FindThis As Variant, _
LookIn As Range, _
SheetRange As String, _
OffsetColumn As Integer) _
As Variant

Dim Sheet As Worksheet
Dim strFirstSheet As String
Dim strLastSheet As String
Dim Sheet... Read more

16 more replies
Relevance 58.22%

I am using mac numbers and am trying to get a column from one sheet to automatically copy onto the next sheet. What formula do I use to accomplish this?

Answer:mac multiple sheets formula

I'm not sure what you mean by mac numbers but this formula will "duplicate" whatever is in Column A on Sheet1 in Column A on Sheet2:In A1 of Sheet2 put:=SHEET1!A1and drag it down.Is that what you were looking for?

2 more replies
Relevance 58.22%

I am trying to create a summary sheet based on several sheets in my workbook. I say several, because the number will be changing throughout time. Therefore, I cannot easily create named ranges. Each sheet has the same layout- Column B contains Dates, C- Principal Payments, D-Interest, and F- Balance. I cannot use a simple sumif function, because the starting date will not always be the same. I originally tried a UDF based on this post: ; however, that returns the first instance instead of the sum.Unfortunately, I don't know much about VBA and can't alter the function on my own. Any suggestions are welcome! Let me know if this wasn't clear and I can try and provide more information

Answer:Vlookup with sum across multiple sheets

As far as using a Named Range, you can create a dynamic named range to refer to all sheets via a couple of different methods.This first method will create a Named Range across all sheets. StartSheet and EndSheet are the actual names of the first and last sheet in your workbook. As long as the name of the first and last sheet don't change, it will always include all sheets in between. In other words, as long as your new sheets are inserted between the first and last sheet, the named range will "update" to include the new sheet.=StartSheet:EndSheet!$C$5:$C$16If you can't control where the new sheets will be added, or if they will always be added after the last sheet, then try this event triggered code:First, create a Named Range using the method above so that it Refers to the first and last sheet and the desired cells. I used AllSheets as the Name.Place this code in the ThisWorkbook module and it will fire whenever you add a new sheet, regardless of where you add it. It will reset the Named Range to include all sheets.Make sure you update the R1C1 reference to reflect your desired range.Note: If you move the last sheet from the last position to any other position, the Named Range will not change and will then refer to the first sheet through the new position of the sheet you moved. If you need to be able to move sheets arounds, specifically out of the last position, then we'll need a method that fires the code based on some other event so that the name always gets updated with... Read more

3 more replies
Relevance 58.22%

I was wondering if Word has the ability to contain multiple sheets with tabsdown the bottom to switch between them, similar to what Excel has with it'sworksheets in one file.It would be nice for some projects, wouldn't it?

Answer:Multiple sheets in word

Word doesn't use sheets because the idea of a Word document is that you can print it, which would be difficult if it had different sheets.If you want the effect then just use Excel as a word processor, it works in a similar way & I've seen many documents produced like this."I've always been mad, I know I've been mad, like the most of us..." Pink Floyd

13 more replies
Relevance 58.22%

Hi, Can I get help with a VLOOKUP formula I have a excel spreadsheet that has 3 sheets in it I am trying to look up data from sheet 3 from column D rows 2 through to 76 and if it matches in sheet 2 data from column H rows 2 through to 117, I would like it to put a Y in a column J where the data matched and a N if no match found from sheet 3, I would also like it to put temperature reads in if matched in column K getting information from sheet 3 from column F rows 2 to 76. Thanks

Answer:Help With VLOOKUP Across Multiple Sheets

Before we address your question, here's a posting tip:When posting in a Help forum such as this, please try to use a subject line that gives us a little detail about your question. Considering the hundreds of questions posted in this forum, if everyone used a generic subject line such as "Help With Excel Formulas" we wouldn't be able to tell one question from another.I have edited the subject line for this question.DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 58.22%

I'm trying to create a lookup to search for one of our batch numbers, which could be on any of over 40 sheets within one workbook.Using the numeric batch number the entire workbook needs to be searched and then return the contents of an adjacent cell (which is decided by column number and can be text,numeric or both). I don't want to have to input over 40 sheet name! Can anyone help?

Answer:Lookup across multiple sheets (40+)

Do you need to search the entire contents of every sheet for the desired batch number or would it be in a specific (and same) column on every sheet?

9 more replies
Relevance 58.22%

Hi Everyone,

I am working on a project and have copied the macro below to delete lines which contain "0" in the 8th column.

It seems to work well, but I need to get it to run across 6 sheets in the same workbook.

Can someone show me how to do this?

Thanks for your help.

Sub Delete_0_MarkedRows()

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Dim lastrow As Long, r As Long

lastrow = ActiveSheet.UsedRange.Rows.Count

For r = lastrow To 1 Step -1

If UCase(Cells(r, 8).Value) = "0" Then Rows(r).Delete

Next r

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

End Sub

More replies
Relevance 57.4%

I am looking to do something similar to a Vlookup, but I have 9 sheets through which I want excel to look. Basically, I have a list of names with data that carries through 9 sheets, and then a shorter summary of specific names. I need to get the data on to the summary sheet for each name. Any help would be much appreciated, thanks.

Answer:Excel Vlookup over multiple sheets

I'm a bit confused.I see 3 possible scenarios based on your question and I don't know which one you have:1 - Data for each name can be found on all 9 sheets and you want all of it brought into the summary sheet.2 - Data for each name can be found on 1 or more of the 9 sheets and you want all of it brought into the summary sheet.3 - Data for each name can be found on only 1 sheet, but you don't know which one and you want it brought into the summary sheet.For scenarios 1 and 2, where data might be found on multiple sheets, how do you want the data to appear on the summary sheet? In one cell? In multiple contigious cells? In a seperate column from each sheet, like a table? Something else?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 57.4%

I have a workbook with the first sheet setup as a form which is supposed to populate 3 other sheets. I created a submit button that sends the data to the first sheet, but I'm not sure how to get it to send to the other 2. possible case statement?
Some of the data is duplicated amongst the sheets while other is omitted. any guidance / help would be appreciated.
I have used the offset function to find the next empty row.
i am attaching a sample workbook

Answer:populate multiple sheets from one form

After the sub,
Dim rNextC1 as range

where you have the personal details:
wsdata2.Range("A" & rnextC1.row).value = wsForm.cells and whatever for vehicle, same for name, surname and colour
wsdata3.Range("A" & rnextC1.row).value = wsForm.cells and whatever for drink, name, day and dress

2 more replies
Relevance 57.4%

Don't know if this should be an XP issue or Applications, but . . .
I've recently changed employers, and therefore have new Office 2003 apps (shared through a Citrix MetaFrame environment - yuk!), and have an issue in Excel. When I have multiple workbooks open, I have only one Excel button on my taskbar, and the only way to move from one sheet to another is via the "window" dropdown on the toolbar, or Arranging windows through the toolbar. In MS Word, on the other hand, I get what I'm used to - multiple taskbar buttons, one for each file open.

I've tried ungrouping my similar taskbar buttons, but no help. My single Excel taskbar button doesn't list multiple sheets anyway, just the topmost sheet.

My guess is this is a function of working through Citrix MetaFrame, but I would really like to get back to the old way of quickly switching between worksheets by clicking a taskbar button.

Any ideas???

Answer:Multiple Excel sheets, but only 1 taskbutton

In Excel: Tools - Options - View tab; check the "Windows in Task bar" box.

2 more replies
Relevance 57.4%

Hey guys,I need some help. All of my data is in Sheet one listed as:Column A - DateColumn B - NameColumn C - Amount ReceivedColumn D - Amount PaidColumn E - Balance OwedAll of the headers of those columns would be the same on each worksheet. What I need help with is that I need to create new worksheets for each Name Sheet 1 data. Looking around came across the codes by Humar as MovetoTab. worked exactly as I would have wanted. But would like all cells to be autofit to the cell content once all the new worksheets have been created. Thanks in advance. The code by HumarPublic Sub MoveToTab()Dim rngStart As RangeDim rngEnd As RangeDim rngCell As RangeOn Error GoTo ErrHndWith Worksheets("Source") 'set start as B2 i.e., after heading row in column B Set rngStart = .Range("B2") 'set end - last used row in column B Set rngEnd = .Range("B" & CStr(Application.Rows.Count)).End(xlUp) 'setup destination worksheets 'either create new if they don't exist, 'or clear existing data if they do 'loop through cells in column B For Each rngCell In Range(rngStart, rngEnd) 'test if tab exists On Error Resume Next If Not Worksheets(rngCell.Text).Name <> "" Then On Error GoTo ErrHnd 'No worksheet of this name - so create one Worksheets.Add After:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = rngCell.Text Else On Error GoTo ErrHnd 'clear data... Read more

Answer:Copy Data to multiple sheets

Hi,Between the lines: End With and Exit Sub, add this code (you can put the Dim line up with the other Dim's at the start of the macro):'set the width of columns in all worksheets (except "Source")
'using columns A to E
Dim wsEach As Worksheet
'loop through all worksheets
For Each wsEach In ActiveWorkbook.Worksheets()
If wsEach.Name <> "Source" Then
'not "Source" worksheet, so auto-fit columns A to E
End If
Next wsEachIf there are other worksheets in the workbook that you do not want to 'auto-fit' then use something like this:If wsEach.Name <> "Source" And wsEach.Name <> "Sheet2" ThenRegards

4 more replies
Relevance 57.4%


I am working on a workbook which contains (at the moment) 13 calculation sheets and 1 master sheet.

The 'mastersheet' contains information such as dates etc which are pulled through to the same places in all the other sheets.

I am looking for a way to hold a formula in a cell on the 'mastersheet' and have the formula, not the result of the calculation, pulled through to the other sheets.

The formula is a simple one - =c24/37*f8

Each of the calculation sheet has the figures in c24 and f8 to perform the calculation there is nothing in those cell references on the 'mastersheet' and I am getting the result 0.00 showing in the cell.

If I use =mastersheet!$c$2 on the other sheets i get the result and not the formula...

can anyone help me please... I'm sure it is something really simple but I just cannot figure it out... many thanks... Jon

Answer:Excel formula to multiple sheets...

Group the sheets you want to replicate the formulas on, then create the formulas on the active sheet and they'll "flow through" to the other sheets in the group.

1 more replies
Relevance 57.4%

I am preparing year end numbers for one of our clients. I have several different worksheets I am working on in one workbook. I have to take totals form one worksheet and add them to another to get final totals. My problem is that when I try to copy and paste the one set of totals, they are not the correct numbers. For instance: the total is 687,862.00, when I paste to the next worksheet is is 677,452.00. Does anyone know what I may be doing wrong, and what I need to do to fix this.

Answer:Need Formula To Sum Values From Multiple Sheets

Are you Pasting the Formula or are you Pasting the Values?From the Edit menu, select Paste SpecialThe Paste Special dialog box should appearYou can now select either to Paste just the ValuesorPaste the FormulaThe default behavior is to Paste the Formula.And just so you know, Excel questions are best asked in the Office Software Forum.MIKE edited by mmcconaghy

4 more replies
Relevance 57.4%

Give me a hand to impress the boss!

I have a column which has a list of different values. For each value in this column i wish to create a new sheet and name the sheet the same name as the value in the column.

For this i have set up a do/while loop , however, after about 27 turns, it stops.

My guess is that it is something to do with the sheet Name property, or the number of sheets that are there. i.e. in the VBA project box on the code screen, it appears like this
Microsoft excel objects
etc until

Here is a summary of the loop;

Dim strValue1, strValue2 As String

Do While intCounter < intNumSheets
strValue1 = ActiveSheet.Range("E" & intCounter).Value
strValue2 = ActiveSheet.Range("E" & intCounter + 1).Value

If strValue1 <> strValue2 Then
Application.CutCopyMode = False
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = ActiveSheet.Range("E" & intCounter + 1).Value
End If
intCounter = intCounter + 1

Email me

Answer:Excel Macro - Multiple sheets

Yep. Bad news when that occurs. After while, you won't be able to open the workbook. You must be using 97, huh?

Each time you make a copy of a copy, it adds the one.

Try doing it this way instead:

Create yourself a sheet you'll use as a template.
Copy ONLY that sheet each time you create a new sheet.
You can hide the template sheet.

Don't ask me for the code, LOL!
I don't code, but I can troubleshoot it pretty well. I know that makes no sense...

1 more replies
Relevance 57.4%

Hello,I am new to VBA and I need help writing a macros to create many new worksheets from rows of data contained in a workbook that has several tabs. My data is from multiple climate stations (each sheet (or tab) contains data from one station), and I need to create individual new worksheets by date with data from multiple stations. I tried using the macro recorder, but the scripts are so large that I need several modules to complete the work. My data looks like this:Date Value1 Value2 Value3 Lat LongJan80 123 155 50 444 555Feb80 156 547 952 444 555This is the type of data on a worksheet named "Site1" and the format is repeated for each of 45 sites in a workbook. What I need are individual worksheets named "Jan80Value1" with the Value1, Lat, and Long data from that row from each Site tab in the workbook. Repeat this for each date in column 1 and for each Value. In the end I will have over 1000 new worksheets (saved as individual files) named "Jan80Value1", "Jan80Value2", "Jan80Value3", "Feb80Value1".....Can anyone help me write a script to loop or iterate through the steps?Thanks.

Answer:Create new sheets fro multiple tabs

Hi,This is quite a complex task.First you need to go through multiple worksheets and find all the dates. For each date create a new worksheet named with that date plus the data that follows it (Value 1), unless a worksheet with that name exists,thenGo through all the worksheets again, and copy the data to the worksheet with that row's date + value 1 as its name.This macro assumes that all worksheets with site data start with "Site", e.g. "Site1"Worksheets that do not start with "Site" are ignored.As changes made by macros cannot be undone ensure that you test this macro on a copy of your data. Always make a copy of your data before running the macro on your actual data.Option Explicit

Private Sub DateTabs()
Dim wsEach As Worksheet
Dim rngEnd As Range
Dim rngCell As Range
Dim strWsName As String
Dim rngDestEnd As Range

On Error GoTo ErrHnd

With ActiveWorkbook
'create worksheet names
'loop through all worksheets
For Each wsEach In .Worksheets()
'only look in worksheets named "Site ..."
If Left(wsEach.Name, 4) = "Site" Then
'find end of data in column A
Set rngEnd = wsEach.Range("A" & CStr(wsEach.Rows.Count)).End(xlUp)
'loop through cells in used range (column A)
For Each rngCell In wsEach.Range("A2", rngEnd)
'create a worksheet name from text in columns A & B
strWsName = rngCell.Text & rngCell.Offset(0, 1).Text
'test if worksheet exists
... Read more

2 more replies
Relevance 57.4%

I have 40 sheets with the names of all the golfers in our league in excel. The layout on all the sheets are the same and I would like to run a macro that sorts dates on all sheets simultaneously. Any suggestions would be appreciated. Thanks!

Answer:Getting a macro to work on multiple sheets

You can't sort the dates on all sheets "simultaneously" but you can sort them sheet by sheet.Sub SortDatesBySheet()
For ShtNum = 1 to Sheets.Count
'Example code to sort data
Sheets(ShtNum).Range("A1:I5").Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
End SubSince it's real tough to see your spreadsheet from where we're sitting, that's about as specific as we can be.

6 more replies
Relevance 56.99%

Hi,I am currently using the code below to find multiple values and the copy/paste into another sheet. The purpose of this code is to retrieve a list of genes (small words) from huge data set. I tell you the description of this code: This code is for one gene list (a list of small keywords) only, which is present in sheet3 column 1 (beginning with A2). All the data which retrieve from Huge Data set (present in sheet 1) is copy on the sheet 2. As a result new sheet is made named "sheet 2". Now I have 6 gene lists (6 lists of small keywords), now i have to make 6 separate sheets for all these gene lists, Like I did in previous work for making only one sheet that is sheet 2. Now here, I want 6 separate sheets for each gene list. Kindly tell me the code for this problem.Sub OrderFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column B, copy it too the next row in Sheet2
With Sheets(1).Columns("B")
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
End With
End Sub
message edited by ... Read more

Answer:Fint multiple words and paste them in different sheets

A little mistake is there in the title, a word is "Find" not "Fint"

6 more replies
Relevance 56.99%

good day

i wish to count how many times a specific value occur on a specific date across different sheets. i am attaching a sample workbook.

in the workbook, the dates are extracted from the various sheets and added in cells C3:L3 (or as many dates there are)
B4:B are constants that will remain there and the values that needs to be found and counted
the values being counted are always in column F in the different sheets.

either code or a formula would work, pivot tables not ideal.

your assistance is highly appreciated

Answer:count unique values across multiple sheets

answer at this link here...

2 more replies
Relevance 56.99%

I have twelve sheets that each have a total score on cell G10 that I want to have copied to a thirteenth sheet in a specific column (F5-16). What would I have to do to make that happen? The G10 cell is an auto-summed cell, if that makes a difference. Also, is there a specific way to create formulas? It seems like everyone's so super smart, just figuring this stuff out! It's awesome!

Answer:Conditional formatting of cells in multiple sheets

=, clicking sheet A G10 and pressing EnterThat's the easiest way.MIKE

7 more replies
Relevance 56.99%

I have a workbook that has 11 sheets. The first is an asset list of 6000 items with serial numbers (master list). Each other sheet has a few serial numbers and extra information. All the master list is not listed in the seperate sheets so i need to compare all 10 sheets to the master list and if the serial numbers match the master list transfer all the data to the master list next to the corresponding serial number.... Thanks

Answer:Excel: Comparing multiple sheets to a master

I'm a tad confused.Are you saying that the master list is missing some of serial numbers that can be found on the other 10 sheets or that all of the numbers are there but the data is not?In any case, before we could offer any specific code, you'll need to provide some details, such as the layout of the sheets.e.g. What column are the serial numbers in? Is it the same on every sheet, including the master? Does transfer the data mean copy the entire row or a specific range.The more detail you provide about the layout of the sheet, the easier it is for us to provide a solution.

2 more replies
Relevance 56.99%

Does anyone have a macro that can be used "generically" in different workbooks with different number and names of sheets, to protect all the sheets at one time.

I'm getting tired of having to protect or unprotect one sheet at a time in workbooks that have anywhere from 4 to maybe 14 sheets, when I'm making design changes.

So far, the only macro I've been able to come up with is:

*** Repeat for each sheet by name***

Of course this won't work from my "Personal" macro library on other projects because there are a different # of sheets, and they're not named the same.


Answer:Excel Macro to protect multiple sheets

8 more replies
Relevance 56.99%

Hi,I'm trying to work on a GoogleDocs Excel type spreadsheet and am a little stuck with a calculation. (It does seem to be able to do most of the things that Excel can do, but not everything.)I'm trying to have one sheet for each month of the year and another "main" sheet.Each monthly sheet pulls the users from the main sheet, and shows the points that each user has accrued for the month. The method that points are added to the sheet is by pasting the result of a script - but as sometimes there are new users from one month to the next, and some users aren't on the subsequent month, this isn't likely to be in the same order as the previous month/s...What I'm looking to do is have the 'main' sheet review the other sheets and add all of their points into there, so that they can see where they stand in the group.Here's an excerpt of the headers from the 'main' sheet, in case it helps:Username | Rank | PointsI might also want to sort out a %age change from one month to the next, but I know how to do that already... I was thinking about doing a DSUM type thing, but considering that there are hundreds of users this doesn't seem like it would work without a lot of manual adjustments - and then I was thinking about the VLOOKUP formula instead, but IDK how to put that...Any help gratefully accepted..!

Answer:Google Docs - Sum data from multiple Sheets

Hi,As you have the data in the order Name ... Nickname, VLOOKUP will not work for returning a Name for a given Nickname.VLOOKUP always looks for a match in the first column of the lookup range and returns a result from a column to the right.You could try a combination of Match and Offset=OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1)Match returns the number of rows down the list that it finds a match, and then this number is used as a row offset, for the Offset function, and -1 is the column Offset.If there is no match, then you get an #NA error, so use this:=IF(ISNA(MATCH(B7,Sheet1!$C$2:$C$61,0)),"No match",OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1))Regards

7 more replies
Relevance 56.99%

I have a workbook with 24 sheets. Each sheet has a parts list F11:K100. On sheet 25, I have a list of all the part numbers used on all of the parts lists. I need to be able to look for every part number on every sheet (column F) and tally the required quantities (column K). Sheet 25 has part numbers (Column A) and total quantities (Column B, where the formula will go).Right now, I'm using VLookUp but the formula is getting to be a bit unwieldy. Is there a cleaner way to basically VLookUp every sheet and tally the results?

Answer:Searching multiple sheets and tallying values

1. Your workbook have 24 sheets. Is sheet 25 in a different workbook?2. ...and tally the required quantities. What is required quantities?Are you trying to compare the data in every sheet of Col.F with Col.K of sheet 25 or something else?

3 more replies
Relevance 56.99%

Hi, really hope you can help me with a tricky (I assume) VLOOKUP problem in Excel 2010,I have a spreadsheet provided by a colleague which lists asset information over a number of similar sheets. Each sheet is a room and the sheet lists up to 60 assets along with associated asset data. Each row is a unique entity/asset across the whole spreadsheet. On all these sheets the unique identifier is in Row A and I need to return the value in row W (asset life).I have added a MySheets sheet which lists the sheet names.I have added a master sheet which lists all entities (taken from another asset list) but has older data (which I need to update). I've added a blank column G next to the existing data that I want to compare. For each of the 7000 rows in the Master sheet I need to search for the same unique item in column B throughout all the other sheets (column A). I then need to return the value in column W from whatever sheet it is on into column G of the correct asset in the Master Sheeet. Sorry for the long winded explanation, hopefully it makes some sort of sense. I've tried various versions of VLOOKUP and INDEX (found after google searches) but can't work out the correct syntax. With luck one of you much cleverer individuals may be kind enough to assist.Many thanks in advance.message edited by GaryG

Answer:How to return single value searching multiple sheets?

It can be done, but it's a bit complicated, this page hasa good tutorial on how to get it to work: if it does what your looking to do.MIKE

3 more replies
Relevance 56.99%

Hi,I have intermediate Excel skills and am at a loss. I have a workbook that has worksheets for each month (Jan - Dec). For each worksheet (month), I tracked the donations of members for each week. I now have this workbook, and I need to create a year-end summary for the total donations for each member. Is there a way for me to create a summary page, and have it to where I can type a name and the total donation shows up? Please help!

Answer:Excel formula to search multiple sheets

Since we can't see your spreadsheet from where we're sitting, we can't give you an exact solution.Perhaps these concepts can be adapted to fit your needs. You could use a SUMIF function on each sheet and then SUM all of those cells together on your summary sheet, but that's boring.Let's say I have 4 sheets named Jan, Feb, Mar and Summary.In the Summary sheet I will use B1 as the cell to contain the name of the person I am interested in. (BTW I would use a Drop Down list of names so I wouldn't have to type anything in)Let's say I have data that looks like this on each of the 3 monthly sheets:
1 Tom 2
2 Fred 3
3 Tom 2
4 Fred 3In each sheet I could go the boring route and use this formula in, say, A6:=SUMIF(A1:A4, Summary!B1, B1:B4)Then in the Summary sheet I could use:=SUM(Jan:Mar!A6)That would SUM each SUMIF result and give me the total.A much more elegant way is to do everything on the Summary sheet as follows:In Summary!A1:A3 (or some out of the way place) I would put a list of my monthly sheet names:
1 Jan
2 Feb
3 Mar
Still using Summary!B1 as the cell to contain the name I am searching for, I would put this in Summary!C1:=SUMPRODUCT(SUMIF(INDIRECT("'" & $A$1:$A$3 & "'!A1:A4"), B1,INDIRECT("'" & $A$1:$A$3 & "'!B1:B4")))Make sure you note the difference between the double quotes and the single quotes or this formuala won't work.After each INDIRECT you should have Open Parenthesis, Double Quote, Single Quote, Double Quot... Read more

3 more replies
Relevance 56.99%

It's a brother printer HL-5140 or something. I try to print one document and instead the printer spits out like 30 blank pages. It happens randomly and with various programs. Tried re-installing also. Win xp.


More replies
Relevance 56.99%

I'm not sure that this is even possible, but I'm going to attempt it...
My goal is to have my first sheet as the "Summary", which will have the exact same layout as all the other sheets. Now I'm looking to make a formula where people can add sheets to the workbook and the summary will be able to recognize that and add those new sheets into the summary page.
This is for my job. Basically, each year schools have to fill out information about their buildings and there was talk of some of the schools being interested in having a document where they can go classroom to classroom, gather the numbers they need, put it into a document and then have another section that adds up all that information collectively (aka the summary sheet.)

Is this even possible? Any help would be greatly appreciated!\


Answer:Excel; summary sheet with multiple sheets

Sure it's possible, you will run into the fact that if opened at the same time (network?) you'll have to open it as shared.
I'm not that good at shared files but I'm sure one of the other members does.
If you place a sample with some data we can have a look.

2 more replies
Relevance 56.99%


I have a spreadsheet with multiple sheet to it. I have a VB script that runs on the currently activated sheet. How do I get the script to run on all of the sheets in the spreadsheet?

Answer:Excel, running a scipt on multiple sheets

Below is the code I am running. I can get the script to work as I want the problem is that it take WAY too long. When I run the test script, it takes about 30 seconds to run on the currently active sheet. When I add the Allsheets code to the top to run a loop, it took an hour, and it had only gotten through half of the sheets, 11 of the total 21 sheets (they were done correctly BTW). What do I need to do with the looping code to speed it up? In theory if a single sheet took only 30 seconds, then 20 sheets should only take 10 minutes, not a couple of hours.


Sub AllSheets()

Dim Y As Integer
Y = 1

Do While Y < 22
Call test
Y = Y + 1

End Sub

Sub test()

Application.ScreenUpdating = False

x = Range("A" & Rows.Count).End(xlUp).Row

Range("C2") = "Active"
For Each Cell In Range("A1:A" & x)
If Cell = " DETACHD" Then
Range("C" & Rows.Count).End(xlUp).Offset(1) = WorksheetFunction.Trim(Cell.Offset(-1))
Range("C" & Rows.Count).End(xlUp).Offset(, 1) = Cell.Offset(7)
ElseIf Cell = "Bumpable Buyer " Then
Range("C" & Rows.Count).End(xlUp).Offset(1) = "Bumpable Buyer"
ElseIf Cell = "Canceled " Then
Range("C" & Rows.Count).End(xlUp).Offset(1) = "Canceled"
ElseIf Cell = "Expired " Then
Range("C" & Rows.Count).End(xlUp).Offset(1) = "Expired"
ElseIf Cell = "Pen... Read more

1 more replies
Relevance 56.99%

Hello Guys,

I saw this archived post here and hence, asking this question:
Excel and Powershell Open Workbook with Multiple Sheets

I have written the following powershell script for getting the database status (offline or online):

foreach ($svr in get-content "demo.txt")


$con = "server=$svr;database=master;Integrated Security=sspi"

$cmd = "select state_desc as DB_STATUS,COUNT(name) as [TOTAL DB'S] from sys.databases group by state_desc"

$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dt = new-object System.Data.DataTable

$da.fill($dt) |out-null

$dt | Format-Table -autosize

The thing here is, I want to change this script in such a way that the output is exported to an excel file and as per the number of servers, new sheets should get created and the output get's stored accordingly, related to the server.
I'm not great at powershell, so kindly help me on this.

More replies
Relevance 56.99%

I am having a problem with my HP K80 multifunction printer, it is feeding multiple sheets whenever I print, copy or receive a fax. How do I fix that? I'm fairly handy, is this something I can fix myself? Thanks! Mona

Answer:HP K80 Printing Problem - multiple sheets feed

Usually this indicates the wrong weight of paper or your paper lever, if one, is set to thick paper. Check your thickness level and/or use thicker paper.

3 more replies
Relevance 56.99%

Hi, I have one questions. I wanna ask if is possible to insert multiple checkboxes in all sheets at the same time? I already done many search on internet about this but I gain nothing. It is possible to solve this using excel vba? or there any other way? just wonder. Thanks you for any helps.

More replies
Relevance 56.99%

Hi good day
i have a workbook consisting of several sheets. each sheet depicts data for several critera related to that specific sheet (the row fields are the same across each sheet) which is further broken down into categories specific for that area. in the summary sheet which also share the same rows i should be able to select a category (from a dropdown) which will give me a summary of those fields in each category.
the headings for the categories on each sheet would be 1 to 100. for sheet 1(town1) - area 1, area 2, .., area4, sheet 2(town2) - area 5, area6, area7, and so on.
fields making up the rows for those headings are e.g apples, pears, oranges, etc
so the sheet displays how many of each item was sold in a specific area.
areas might overlap in town sheets as a salesperson from town1 might have sold an item in an area belonging to a different town.
the sheets are named by town (town 1, town 2, etc)
the summary sheet - i wish to see how many apples, pears, oranges was sold in a specific area across all the towns.
if i select say 'area 2' in the area dropdown, it will search across the sheets and display the totals of each of the items sold for area 2.
i have used the formula 'sumproduct' which works well but it only works if i have all 108 areas on each sheet. this is not viable as it opens the data up to errors. i wish to know is there a way if the user select an area as a heading then populate the corresponding item with the amount sold. tha... Read more

Answer:Solved: count values across multiple sheets

16 more replies
Relevance 56.99%

This is about an HP all-in-one printer. The 8610 is similar to other HP printers in the same model series,  8520, 8630  etc. HP 8610 is over a year old, so I can not send it back now. The first year I did a lot of printing with no big problems. But now, it is wasting ink, paper and my nerves. When printing a job, either single or duplex, it will pick up 2, 3 or even more sheets of paper. It is standard 20lb letter paper I have always used.Yes, I have cleaned the rulers time and time again. There does not seem to be any kind of adjustment to correct this. A Google check shows this problem is frequent on HP printers in the model group. The suggestions by HP on forums do not after a real fix.Any ideas?

Answer: HP 8610 picks up multiple sheets when printing.

I always shingle the paper before loading. I mean grip the stack at one end and flap it slightly to make the sheets separate from each other.

3 more replies
Relevance 56.99%

Hy all!I am trying to highlight duplicate entries from different worksheets using conditional formating and is not woking. I've found this formula =NOT(ISERROR(VLOOKUP(A1;bannedworkers;1;0))) but it not works ; i'm using excell 2003.I have two worksheets;in the first sheet on the first column (A) named bannedworkers I have a list of banned workers and in the second sheet on the first column (A) I have a list with workers. I want that when I type a banned workers name in the second sheet, first column, the cell to highlight in the color chosen in the conditional formatting. I've used this formula in conditional formating but it not works =NOT(ISERROR(VLOOKUP(A1;myColumn;1;0))).What is the problem???

Answer:Highlight duplicate entries multiple sheets

You should be able to accomplish what you want, but you will need to use a Named Rangein the first sheet on the first column (A) named bannedworkers Select your range of cells, IE A1 thru A50On the Task Bar/Ribbon Select FormulasSelect Define NameIn the Name Box, give your range of cells an appropriate name like: NameListClick OKin the second sheet on the first column (A) I have a list with workers. I want that when I type a banned workers name in the second sheet, first column, the cell to highlight in the color chosen in the conditional formatting. On your second sheet where you want the Condition Formatting to go:Conditional Formatting 20071) Select your range of cells: IE A1 thru A502) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =COUNTIF(NameList,A1)=1EDIT: Shorter formula: =COUNTIF(NameList,A1)6) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKSee how that works for you.Just be aware that the names have to be Exactly the same, and spaces are considered characters.MIKE

3 more replies
Relevance 56.99%

I need to figure out how to do this:1. I have a workbook page with roster names (last name only) in column A. 2. I have 4 other workbook pages where the people from the roster are being placed for duties.3. I need a search program within that workbook that will scan the names and compare them to the names on the roster page. If a person's name appears on another worksheet then their name box will turn yellow (showing they've already been assigned) and if their name appears two or more times their name box will turn red showing double assignments. Anyone have any help with that? Thanks!

Answer:Excel formula to search multiple sheets

You can do that with Conditional Formatting.My example will only use 2 sheets (not 4) so simply expand the concept to get it to work for any number of sheets.First, since Conditional Formatting cannot reference other sheets in it's formulas, we need to use named ranges.Let's say my Roster is in Sheet1!A1:A5.Lets' say the names I am searching for are in Sheet2!A1:A5 and Sheet3!A1:A5.1 - Select Sheet2!A1:A5 and name the range. I used "mySheet2"2 - Select Sheet3!A1:A5 and name the range. I used "mySheet3"3 - Select Sheet1!A1:A5 and click on Format...Conditional Formatting...Formula is4 - Paste this into the formula Field and choose a Red fill format:=IF((COUNTIF(mySheet2,A1)+COUNTIF(mySheet3,A1)>1),1,0)5 - Click Add, Formula Is, and paste this into the new formula Field, choose a Yellow fill format:=IF((COUNTIF(mySheet2,A1)+COUNTIF(mySheet3,A1)=1),1,0)What this will do is count how many times each name appears in the named Ranges. If the sum of the counts is greater than 1, the cell will turn Red. If it equals 1, it'll turn yellow.Hope that helps!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 56.17%

Hi all,

I'm using excel 2003 and trying to find out if there is an easy way of using data available in multiple sheets without putting in each sheet's name individually.

For example, each 'customer' has 2 sheets within a workbook, identified by a 'customer' number.
eg. sheets for customers 123, 345, 789:
D123 / X123 / D345 / X345 / D789 / X789

I'm actually using the function DCOUNT at the moment, with each 'customer' having their own workbook. When I use the DCOUNT function, EACH CUSTOMER'S workbook needs to be open, which is a pain. (and it craps itself if they're not open)


by using the two sheets for customers (D and X) is there a way to search for D* (like a wildcard which would only use sheets D123, D345 and D789, but not X123, X345 and X789) within the DCOUNT function? Something like:


Any help would be greatly appreciated!!


Answer:Pulling data from multiple sheets within a workbook by using a wildcard

7 more replies
Relevance 56.17%

I have a need to merge data from multiple sheets to create a consolidated list. The content of the data from each of these sheets is in different columns.


Sheet A columns A12:A65535, B12:B65535, D1265535, C12:C65535, I12:I65535, L12:L65535

Sheet B columns L3:L65535, J3:J65535, M3:M65535, N3:N65535, O3:O65535, S3:S65535 (this last column needs to be multiple by 1,000

Sheet C columns L2:L65535, B2:B65535, C2:C65535, D265535, E265535, M2:M65535

This needs to work in either Excel 2007

Help appreciated.

Answer:Excel: Merging data from multiple sheets / different columns

Hi VPCPG, and welcome to the forums

What are you trying to do exactly? When you say you are trying to create a consolidated list, do you mean you want information from different columns to show in one cell? I could do with knowing what kind of data you are dealing with - numbers, text, etc.

6 more replies
Relevance 56.17%

This will take some explaining,

An All in One HP Laser Jet 3015 was donated to me. It is about five years old. The bottom tray with the paper works fine. The top tray for Faxing, Scanning, or Copying documents 95% of the time always pulls multiple sheets through. On line, I purchased a Paper Separator that was supposed to stop the multiple page printing problem. What I did not know at the time, was that the paper separator replacement is for the bottom tray only. All of the documentation for the HP printer with regards to the top tray for Copying, Faxing, or Scanning only suggests that you can clean the top paper seperator with Windex, a small amout of water, or a mild non-abrasive cleaner with a lint-free cloth or paper towel. We did that. Is there a way to replace that top paper separator?

Every once in a blue moon, the printer will feed from the top tray correctly. In fact, shortly after cleaning the top paper separator, it pulled only one page through the feeder about three or four times. Today, back to the multiple pulls of the paper from the top tray. New cartridges, Windex cleaning, and the ordered paper separator have not fixed the multiple paper pull from the top tray. HP also has a scanner assembly kit for $36, which claims to stop multiple pages going through when scanning. Is there any way to replace that top tray paper separator, or should I just be content with having to copy, FAX, or scan documents one page at a time? I have already put $20 into getting the p... Read more

Answer:HP Laser Jet 3015 Always Feeds Multiple Sheets Through Top Tray

I sort of have a work around after studying this in greater detail.

Maybe this will help others with top tray muli-feeds in the same or similar situations. What I do is put the paper in really gently, like "Light as a Feather" without pushing it all the way down into the tray. Than, I check the side of the printer to see that there is a bit of space between the black pieces of paper in the top feeder tray. It seems with that very light feeding into the printer and having that little bit of space between pages, the sensor seems to work better feeding the pages through one at a time as it should. If the pages are seeming bonded together where they look like one sheet, the printer will treat as many as five sheets counting them as one sheet on the feed through.


2 more replies
Relevance 56.17%

Hello Everyone,
I would like to thank Bomb 21 for the help I received with the Excel spreadsheet earlier. I have 2 other dilemmas that I need help with. I am not experienced at all with VB. I searched on the net for a basic answer but all I could find were answers that did way more than I needed. Here it goes. (Please see the attached zipped spreadsheet)

I have a spreadsheet with a VB script that mostly runs as I would like on the first sheet. I would like this script to:

1.) I would like the script to calculate the results it gives me in column D as follows, count the number entries that range in price from $0-$199,999, $200,000-$249,999, $250,000-$349,999, $350,000-$499,999, $500,000-$749,999, $750,000-$999,999, $1000000+ and place those into their corresponding place holders in column G (for each section, Active, Pending, WIthdrawn, Sold etc.)

2.) Perform this script on all of the sheets in the Spreadsheet.
Thank you in advance for your help with this.

Answer:Solved: Excel Question: How to run same script in multiple sheets

16 more replies
Relevance 56.17%



etc... but several thousand rows each with their own "type" ordered by type

how can i create a separate spreadsheet for each type...

dont know what this process is called...

surely there is a faster way than copying each type and pasting into a new xls doc...

(dont want to create tabs)

Thanks for any advice

Answer:Solved: how can you create multiple separate sheets from one sheet xls?

6 more replies
Relevance 56.17%

I am exporting queries from access to excel using Docmd.OutputTo.

I have 13 queries that I need to export into 13 different sheets in an Excel workbook. Unfortunately, each query exports to a single sheet in the workbook, so I am left with one sheet containing the last query exported.

I tried to set up 13 different sheets, each named the same as the 13 queries, but no change.

Anyone have any ideas?


Answer:Access:OutputTo function and multiple Excel sheets

Try something like this using the TrasferSpreadsheet function.

You could modify this simply enough to do what you want.

2 more replies
Relevance 56.17%

Hello everybody,
I searched but could not find an answer to my quest. It might be that I did not define my question in the search well enough, but here it is.
I am trying to get cell info from multiple workbooks with 8 named sheets into one workbook.
Now I am trying to explain myself a bit more:
Workbooks are named WS-01, WS-02, etc
each Workbook has 8 sheets named total, Monday, Tuesday, etc.
each sheet has multiple cells and I need to get info from one cell into a new Workbook with one sheet and the cells are named: Week, Monday, Tuesday, etc.
I know I could do cut and paste but that is to time consuming.
Anybody with some help? It would be much appreciated.

Answer:Excel info from multiple woorkbooks and sheets into a new workbook

Is this an ongoing event or a one time thing? What you want to do can be accomplished with a macro but if this is a one time event it may not be worth the time. If you want to use automatation it would be helpful to post a sample workbook containing your source data as well as a sample destination workbook that the data will be transferred to.


1 more replies
Relevance 56.17%

My friend has set up an excel workbook with a sheet for every month (labelled Jan graphs, Feb graphs, Mar graphs etc) which has 7 pie charts per sheet. The problem is the pie charts are showing the 0% fields and the data labels are overlapping. I have found a macro that gets rid of the 0% labels but I need to repeat the macro for all the 7 charts on each sheet and all 12 worksheets for each month of the year. I am very new to VBA and coding of any type and have looked but can't find the solution.

The macro I am currently using for "Chart1" on "Jan graphs" is below. The charts are labelled Chart1, Chart2, Chart3 etc.

Sub ClearLabels()

Worksheets("Jan graphs").ChartObjects("Chart 1").Chart.ApplyDataLabels _

For Each x In Worksheets("Jan graphs").ChartObjects("Chart 1"). _

If InStr(x.DataLabel.Text, Chr(10) & "0%") > 0 Then
End If
End Sub

Answer:Macro in Excel to repeat for multiple charts and sheets

The obvious solution would be to do an outer loop of the sheets with an inner loop of the charts. My experience with this (I am not a great, or even good, programmer) is that a spend a lot of time making it work the way I want.

I am assuming this is a one-off as described so why not do the obvious very inefficient method. Please ignore if elegance is your object!

Take the body of the macro

Worksheets("Jan graphs").ChartObjects("Chart 1").Chart.ApplyDataLabels _

For Each x In Worksheets("Jan graphs").ChartObjects("Chart 1"). _

If InStr(x.DataLabel.Text, Chr(10) & "0%") > 0 Then
End If
NextClick to expand...

add six copies and then change the chart labels to Chart2, Chart3, etc.

Now select the body of this new macro and copy it 11 times.

Select the copies one by one and do a replace for the month name. (If you are using the MS Visual Basic Editor then Ctrl-H will do a replace within selected text)

3 more replies
Relevance 56.17%

I built in excel and uploaded to google docs a spreadsheet that contains very simple formulas to bring to sumation variables from multiple worksheet from the same cell, example:=SUM(FW:MC!G10) (in Excel)when uploaded it converts to:=SUM([1]FW:MC!G10) and does not work.What can I do to correct this so that I can calculate?

Answer:Formula for Google Docs Sum of same cell in multiple sheets

I don't use Google Docs, but according to this 2011 thread, Google Docs does not allow 3D references. As far as I can tell, you will need to create a SUM formula referencing each individual sheet either before you upload the file or once it's in Google Docs. addition, while researching this, I found a couple of other threads that mention people having problems with even simple formulas changing when Excel workbooks are uploaded to Google Docs. Just be aware.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 56.17%

How can i print multiple worksheets in an Excell 2007 workbook. Thanks in advance.

Answer:Solved: Print multiple sheets in Excel workbook

Hi there!

Yes, you can print multiple worksheets. Just select the ones you want by clicking on the tabs at the bottom of each worksheet that you'd like (holding down Ctrl will let you select more than one, or Shift will select all sheets in between the first and last one that you click on). If you go to your Print dialog box, it should have the 'Active Sheets' radio button selected in the 'Print what' section. This will print every worksheet you've selected. Alternatively, you could select 'Entire workbook' to have every worksheet in the file print out.

Hope that helps!

2 more replies
Relevance 56.17%

I have an issue I have not been able to find an answer to, so I hope someone can help. I have a workbook, where there are many worksheets that i would like to consolidate into one sheet. The problem is I'm dealing with a lot of data. Each worksheet represents a day of the month, so there are 30-31 worksheets for each workbook.

Ultimately what i'm looking to acheive is to some how find a way to compile the date from all worksheets so i can do a quick search and pull from all the data from all the worksheets and see the results in one place. The search results would often find many results (hundreds even), and i still need to see all the data from those results from that row. I like the idea of using a pivot table, but once you use it for multiple sheets i cant get it to show the actual data.

I hope I have been clear with what i'm trying to do, if not, please let me know. Thanks for any help you can provide.

Answer:Solved: EXCEL - Consolidate data from multiple sheets

11 more replies
Relevance 55.76%

Hi everyone,
I am new to this Forum.
I have a workbook with multiple sheets Sheet1, Sheet2, Sheet3, etc.) each with similar information with same column headings. I want to copy multiple rows from each of these sheets into a common Summary sheet based on:

1. if the column value (Column J) is greater than zero

2. if the column (Column B) contains a certain word such as "Total", "New", or "Summary"

I need to create and use two seperate macros one for each of the above conditions.

I hope I was clear enough in explaining my situation. Attached also please find the sample data. Appreciate your help and direction! Thanks a lot!!!


More replies
Relevance 55.76%

I'm really new with macros, but I want to know if it is possible to do the following scenario and how to accomplish it:

I have two sheets in one workbook. On sheet 1, there is an inventory which contains codes, dates, titles, and most importantly, subjects in 6 columns (G-L). On sheet 2, I put the old subjects (from G-L on sheet 1) in column A and the new subjects in column B. I want to utilize a macro to find all the "sheet2 column a" subjects on sheet 1 (match entire cell contents) and replace them all with the new "sheet 2 column B" subjects. Can this is done? How would I do it?

Thank you for any advice you might have, I truly appreciate it!


More replies
Relevance 55.76%


is it possible to group edit multiple excel files ? i mean separate workbooks/worksheets (not sheets within wrkbk)?


Answer:is it possible to group edit multiple excel files (not sheets within wrkbk)?

hi ubamous3,

if you mean, can you have multiple active cells in multiple separate worksheets in different workbooks that will be updated all at the same time, then NO.

what you can do, is link the different sheets to another workbook/sheet and edit that as a singe point of entry.


unless someone has a better idea???

3 more replies
Relevance 55.76%

I have three sheets of data that I want to combine into one based off of criteria in column "A", however I only want the data that is duplicated across each sheet (ie if the three sheet have the name "Bob" in column A, I want the name "Bob" to be in the new list). Some of the sheets may contain duplicated data within itself (ie. the name "John" may be on three different rows in one sheet.) I only want to keep the names that show up on all three sheets, not just multiple times within one sheet.

Any suggestions would be great.

Answer:Solved: Condensing multiple sheets in Excel into one for data duplicated across all s

14 more replies
Relevance 55.76%

I just started working with Macros in Excel on Monday. I've been figuring out what I need to do, searching for threads/blocks and adapting them to my needs. However, since I don't know the basics, I frequently come to dead ends. I apologize for my ignorance, but I have to start somewhere and I need to get this project finished. I have a few parts, and I've included code that I've put together/adapted adjacent to my explination.
I have a workbook with a series of worksheets
The first sheet is a Job List and the list has columns for each type of permit that can be selected for each job.
The idea would be to put an X or some indicator in a column if a certain permit is necessary for the job associated with that row.
Each subsequent sheet is one of the types of permits.
Some jobs need multiple permits.
I would like a macro that, whenever anyone adds a job, it automatically updates the necessary worksheets with the job name.
So if Job Blue needs Permit X and Permit Y, the macro will only add the Job Name “Blue” to worksheets X and Y but not Z.


I've created a formula that recognizes if a cell on the job list sheet has been marked and takes the job name from column A. A macro that could recognize when the formula changes results and subsequently autofills the formula underneath the last cell would also work. I've included what I have been putting together.

I have the following, however the line indicated doesn't work:

[/SIZE][/FON... Read more

More replies
Relevance 55.76%

My bosses want me to create some vb code that moves 4 excel worksheets within the same excel workbook to 4 separate Notepad Documents in a different folder called output. The name of each sheet should appear as a header and a footer as well as the name of the document. It is located in cell J4.The data comes from cells starting at (C7 - infinite, D7 - Infinite, G7- infinite) stopping if there is no value in C. Also there is a yes or no question for each line starting in Cell J7-infinite, if true it should say "Not Null" if not it should say "Null". There are specific formatting issues as well. (see below) Dots are spaces and data must appear exactly as below with Column Name, Data Type, (Size), and Not Null as headings.

Column Name................Data Type....(Size)...... Not Null?
Column Name................Data Type....(Size)...... Not Null
Column Name................Data Type....(Size)...... Not Null
Column Name................Data Type....(Size)...... Not Null

More replies
Relevance 55.76%

So first of all let me thank everyone that had input on my last post....i certianly appreciate the help.
so heres what i got going now. i have formatted the class rosters that will be used and i have a master data base of numbers that automatically updates each time a new class is added. and while some ppl think its great others are like well why cant it automatcally populate a list of everyone who failed, got dropped, or didnt show up. so thats what i need to do. i need some vba help on this one ( i actually got them to let me use macros now). i have the class rosters set up to where if a student does not show up then the row get highlited red and yellow if they get dropped, and i will need to add another color like blue for fail. anyways, the workbook is built like this: there is an annual training sheet ( total of all monthly numbers), then 12 monthly training sheets, and 52 weekly training sheets and then an unknown number of daily class rosters. looks something like this:

/ANNUAL//JAN 2011//3-7 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//10-14 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//17-21 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//24-28 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//FEB 2011/......ECT.

ok so what i need to do since the tabs that say "class 1, class 2" and so on will actually have their own unique name depending on the date, type of class and location it was taught. is to be able to have a vba macro (that i will use on each weekly tab and just change the fi... Read more

Answer:need to search multiple sheets and copy based on cell color

If it's a macro, you can test if .font.color = vbred then .... if .font.color = vbgreen ... etc.

If other then you would have to list the color value or colr index as a variable and test on that.

3 more replies
Relevance 54.53%


I have a Microsoft Excel 2003 file that contains two spread-sheets. In one, I have a list of clients and a column next to each client name that I want to display the amount of money made from that particular client. Then, on the second spread-sheet I have a list of all credit and debits relating to the various clients. So, we might have received $100.00 from a client (and that would be in the credit column next to the client name), and then we may have spent $50.00 on that client (and that would be in the debit column next to the client name). So, obviously, the total amount made from that client would reflect the credit minus the debit.

Anyway, what I need is for the cell on the first spread-sheet that says the total made for that client to look at the second spread-sheet, look for any row that has that client's name and then adds the credits and subtracts the debits, then leaving the total back on the first spread-sheet.

I hope that makes sense. I have attached a demonstration to help, and I have also done the formula for the first client to show you what outcome I am looking for (although the formula is not what I want because it doesn't automatically add all of the rows from the second spread-sheet that share that client name).

Anyway, I hope I've explained it well enough and if anyone can help me I'd really appreciate it!

Answer:Solved: Adding Selected Values Together Over Multiple Work-Sheets In Microsoft Excel

hi blujein,

Attached is a quick solution.

Copy the formula I have added to the Total Amounts spreadsheet in Col B, down in new rows as you add new clients.


3 more replies
Relevance 53.71%

I have a range of cells that can be Fail, 70, 85, or 100. I need to have so if one of the cells has the lesser value displayed but I don't know how to work it. So if one of the 7 cells is fail, it should be fail. If no fails, but some have 70, then 70. If no fail or 70, then 85....

Answer:IF Range Contains A Certain Text Return multiple diff. Text

Assuming your range is A1:G1, try this. Modify as required:=IF(COUNTIF(A1:G1,"Fail"),"Fail",MIN(A1:G1))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

5 more replies
Relevance 53.3%

There is probably a very simple solution to this problem but I am banging my head against a wall with it at the moment:
I have an Excel document with 4 sheets. I want to be able to send this document to an individual with three of the sheets concealed. I would like them to be able to enter data into the one sheet that is visible and then send it back to me.
How can I make this happen?

Many thanks

More replies
Relevance 50.02%

Hi there,I need a macro to create new spreadsheets based on sorted data, pulling all of the data assoc. with that # onto a new spreadsheet . Column B has facility numbers (multiple rows per facility number) at each change in Column B, I need all the same numbers copied to a new worksheet (worksheet would then be named whatever number it pulled).Example (Sheet1)Column1 Column2 Column 31 1101 Someplace1 1101 Someplace1 1102 Someplace else1 1102 Someplace elseAll of the '1101' need to be on a new sheet named '1101' all of the '1102' need to be on a new sheet named '1102' etc. This number goes all the way to 1300 so I would end up with 200 different sheets...Is there any hope for me??

Answer:Filter data onto new sheets, re-name sheets

Hi,The following macro will create new tabs for each department and copy the information for each department into the appropriate tab.The headings on the source worksheet will also be copied to the first row of each new worksheet created.The source data worksheets must be named SrcData(or change the tab name here'name of source data worksheet (tab)
strSrcSheet = "SrcData")The Macro assumes that department ID's are in column B starting on row 2 (cell B2) on the SrcData sheet.Data starts in row 2 to leave a row for Headings.It also requires that there is no other information in column B below the last department ID.Here is the code:Option Explicit

Private Sub DeptTabs()
Dim strSrcSheet As String
Dim rngSrcStart As Range
Dim rngSrcEnd As Range
Dim rngCell As Range
Dim strLastDept As String
Dim intDestRow As Integer

On Error GoTo ErrHnd

'name of source data worksheet (tab)
strSrcSheet = "SrcData"

With ActiveWorkbook
'setup source range in column B
Set rngSrcStart = .Worksheets(strSrcSheet).Range("B2")
Set rngSrcEnd = .Worksheets(strSrcSheet).Range("B65534").End(xlUp)

'set destination row counter
intDestRow = 1
'set last department name
strLastDept = ""

'loop through cells in column B
For Each rngCell In Range(rngSrcStart, rngSrcEnd)
'test if department ID change
If rngCell.Text <> strLastDept Then
'create new sheet
.Worksheets.Add After:=.Worksheets(Worksheets.Count)
'name new she... Read more

3 more replies
Relevance 50.02%

I am new to excel..
I have 50 sheets in excel i want to combine all sheets and put them in one sheets..They all have same can I combine them instead of copying and pasting them..
They r all in *.xlsx format..
In there any particular language to do that..I have no idea..please help me..

More replies
Relevance 50.02%

I have a master sheet that we are using to update pricing. What I would like to do is go through all the worksheets in the workbook and find the matching descriptions and update a "Purchasing Notes" cell value with the current price and date that I have combined in another cell. Is this possible?

Answer:Update Cell Value on Multiple Sheets that Match Another Cell

What do you mean by "find the matching descriptions"? Are they in all different locations on different sheets? Can you not simply use this formula?=SHEET2!A1I think we are going to need some more detail related to your workbook before we can offer a solution.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

4 more replies
Relevance 47.56%

Hello All,

I am experience severe lag from 12noon to about 4pm while on the Yahoo Multiplayer Card Game (Spades) site. But I do not have any lag what so ever in the evening.
This had been going on for the past two weeks.
There has been no other changes to the system or cable provider.

None of the other players on the server are experiencing this lag.

Your 2 Cents is Important.

Answer:Lag with distinct pattern

The more people using cable, the slower it gets. I'm guessing that the game is a lot more popular between those hours, but also Internet traffic in your neighborhood.

2 more replies
Relevance 47.15%

Long time computer user but first time with problems like these. I have two laptops, both of which I think have been infected by viruses which have shut down their ability to get online.

Computer 1--Lots of popup virus alerts a while back. Scanned everyday. Kept coming back. At first it would not allow me to go to websites a second time. For example, if I went to then tried the next day, it would not recognize it. That continued for a while, and now it will not open any webpages. It says it is recognizing the wireless router, or the cable when plugged in directly. But no webpages can be opened. Any ideas?

Computer 2--Similar to the first but recently it did 2 weird things. First is it looks like it reverted to an older version of Windows. The task bar at the bottom is no longer blue with the Start button Green and the 4 colored windows symbol next to it. Now it is a thin gray bar, and the start button is a distinct gray button. The font and styles of lettering when you click on things is also very large. The most annoying however is when you go to the connections page it recognizes no connection. Not the wireless router, not the cable when plugged in directly, nothing. So I cant get online at all.

Both computers were used on this network at home and many others at work, in coffee shops, hotels, etc. So they have both gotten online before but just not now.

Any help on either problem would be greatly appreciated.


Answer:2 distinct problems with getting online

ok i am not too sure about computer 2 but i think you may have some form of rogue antispy, were these popups from the internet or from one of your antivirus/spyware/malware/adware ect. ect. programs? what are the names of the viruses they say u have, and if they are trying to make you buy them whats the name of these programs?

1 more replies
Relevance 47.15%

*** Problem, the Case Description is a dynamic Field in which there user enters notes a myriad of ways. I am tasked with identifying the pests within the notes and filling in the Pest Type Field. Conundrum: There is some overlap in how the data is entered, for instance I filter for the word Fly and Fly Problem - The Pest type is flies in both cases. I am trying to enter only distinct Pest Types. If a Pest type is already entered, the same pest type should not be entered into the Pet Type Field. This is happening for other Pest Types as well. I have tried to use code that would not enter a Pest Type if it is already in the Pest Type field. Any help in accomplishing this would be appreciated. There is normally between 2500 - 3500 records to go through. I have been doing the various 'filtering' manually and by using some simple code, hope to cut the 'Labor' time dramatically.

Sub Freemanaddpest_text()

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p

'check for Ants'
If InStr(1, Range("n" & i), " ANTS ") > 0 Then Range("m" & i) = "ANTS"
If InStr(1, Range("n" & i), "ANTS ") > 0 Then Range("m" & i) = "ANTS"
If InStr(1, Range("n" & i), " ANT ") > 0 Then Range("m" & i) = "ANTS"
If InStr(1, Range("n"... Read more

Answer:Solved: Enter distinct value

I am not sure what help you need as the macro appears to do what you want.
What more do you need it to do?

2 more replies
Relevance 46.33%

My boss is looking for clearly stated answers to the following questions; Can a client machine have multiple applications installed that use BITS and if so can each application have its own independent BITS settings? Again, if this can be done, where and
how can it be accomplished?


More replies
Relevance 46.33%

I have a table that has fields called ID combo, Line1, Line2, Line3, Line4, and TotalVolume. Line1, Line2, Line3, and Line4 are generated by all possible combination method. However, the order doesn't matter so I am trying to get rid of it. These 4 lines come from the same list. For example, I have A B C D E F. I need to make all possible combination for 4 lines so it can be:

But what I don't want is, ACBD or ABDC or ADBC or any combination of those. There is a reason that this is important. Let say each letter is assigned a number A=1 B=2 C=3 D=4 E=5 F=6. So the sum of each different combination should be unique. But ACBD and ABDC will have the same sum as ABCD. Now I have created a list with all possible combination as well as the sum for each one. I am trying to remove the redundant combination like ACBD and ABDC, etc.

Since each different combination should have a unique sum, I have tried to Select Distinct of the sum, but when I add other fields to this like ID Combo or Line1 Line2, etc. I get the whole table again.

I have attached the database. See if you guys can help me. If you need more clarification, just ask me.

More replies
Relevance 46.33%

I am having trouble coming up with a way to display certain data in excel. How do you display a list of distinct values in a column with their respective counts. For instance, let's say I have a column that contain 40 values. I want to create a report that lists each distinct field in the column and how many occurences there are. Somthing like the following:

State Count
MD 10
DE 15
VA 25


Answer:EXCEL -- Distinct field counts


Or better yet....

Put MD, DE, VA going down a column.
We'll assume a list of each state's abbr is in Column F starting at row 2, and you're counting the state abbr's in column A:


1 more replies
Relevance 45.92%

I'm completely new to Access, and am having trouble with distinct(count) as Access does not like this as the first select statement. Any advice is appreciated, thanks.

Table contents include this:

Product AMT Status Vendor
Banana 1.01 Yellow 123456
Banana 2.02 Yellow 123456
Banana 2.02 Green 987654
Apple 5.00 Yellow 443322
Apple 6.00 Yellow 332233

I want to query and return results like this:

Product Vendors Trans TTL Status
Banana 1 2 3.03 Yellow
Banana 1 1 2.02 Green
Apple 2 2 11.00 Yellow

Query that does not work:

SELECT product, Count(distinct vendor) as Vendors, Count(vendor) as Trans, Sum(AMT) as TTL, status
FROM myTable
GROUP BY product, status

More replies
Relevance 45.92%

What I'm looking for is not to simply change the whole userdata to another drive, but to create separate drives for each users, and have their application data, my doc, etc folders on their own drives.
Sadly, from looking at environment variables though I think it's not possible.. What do you think ?
My goal is to manage drive space in logical partitions. For example, I don't want to have the whole userdata directly on D: 's root, since nothing would prevent anyone from creating D:/new_folder for example..
Any idea ?

More replies
Relevance 45.92%

I have a table with the following:

id valx valz
1 20 15
1 20 15
1 20 2
2 10 12
2 9 5

id 1 has a duplicate record.

i want to select all records and then sum the value of valz but in cases where an id has a duplicate, i only want the one record included in the sum. so the result should look like this:

id valx sum(valz)
1 20 17
2 10 12
2 9 5

any help is greatly appreciated.

More replies
Relevance 45.92%

Hi. My laptop has an integrated microphone (SoundMAX Integrated Digital HD Audio).

I have an external microphone with a 3.5 jack. When I plug it in, Windows, instead of displaying two microphones, displays only the external one, but also recognizes it as SoundMAX Integrated Digital HD Audio.

Is there a way to separate these two microphones?

Note: When I use an USB adapter, Windows recognizes both mics. But I'm trying to know if it's possible to find a solution without using an adapter.

BTW, I'm using Vista Ultimate x64.

More replies
Relevance 45.92%

I'm likely way overthinking this, (as per usual), but does anyone know of a good application that can:
Capture audio from an application while ignoring audio from other applications.
Allow for individual panning, if capture isn't possible within said application.

I'm trying to get audio feeds from several sources, and get a professional sounding studio sound as you would find in a studio. This would be for interviews done over the web.

So say I have 4 individuals all talking through my computer in an application or varied apps, I can pan the audio channels seperately.

Can someone tell me if this is likely, or if I'm going in the wrong direction. I would just get some hardware, but this gets significantly more expensive.


Go figure, I just found this: It looks quite good, I'll see about trying it out but it looks exactly like what I was looking for. Feel free to go ahead and post any ideas or thoughts in any case.

More replies
Relevance 45.92%

What I'm looking for is not to simply change the whole userdata to another drive, but to create separate drives for each users, and have their application data, my doc, etc folders on their own drives.
Sadly, from looking at environment variables though I think it's not possible.. What do you think ?
My goal is to manage drive space in logical partitions. For example, I don't want to have the whole userdata directly on D: 's root, since nothing would prevent anyone from creating D:/new_folder for example..
Any idea ?

More replies
Relevance 45.92%

Hello, I'm struggling with this problem. I need a formula that reference a value starting in $H4, looks for it across multiple worksheets and returns the worksheet(s) name or, even better, also the cell(s). There are 5 worksheets in total, the value will be text, not numbers. Thank you, Silvia

Answer:Look for a text value across multiple worksheets

In the previous macro I used the Cells method to reference the cells where the Sheet Names and Cell Address would be placed. The Cells method has 2 arguments:Cell(row_index, column_index)Cells(1, 1) refers to A1Cells(2, 1) refers to A2Cells(1, 2) refers to B1 as does Cells(1, "B")I held the row_index argument constant and used a variable to determine the column_index argument. Whatever the current value of dstCol is, that is the column that will contain the next Sheet Name or Cell Address but always in Row 4.Cells(4, dstCol)In this version, I'll hold the column_index arguments constant ("T" and "U") and use a variable for the row_index argument.Cells(dstRw, "T")
Cells(dstRw, "U")You could also use this, since Column 20 is Column T and 21 is U:Cells(dstRw, 20)
Cells(dstRw, 21)Try this version:
Sub FindValues()
Dim dstRw As Long
Dim c As Range
'Initalize Destination Row variable
dstRw = 10
'Loop through Sheet 2 through last sheet
For shtNum = 2 To Sheets.Count
'Search entire sheet(s) for H4 value
With Sheets(shtNum).Cells
Set c = .Find(Sheets(1).Range("$H$4"), lookat:=xlPart)
'If value is found, place Sheet Name and Cell Address in the
'next Row in Columns T & U
If Not c Is Nothing Then
firstAddress = c.Address
dstRw = dstRw + 1
Sheets(1).Cells(dstRw, "T") = Sheets(shtNum).Name
Sheets(1).Cells(dstRw, "U") = c.Address
'Continue searching individaul sheet(s... Read more

9 more replies
Relevance 45.92%

hi guys,

having a bit of a problem trying to highlight multiple text in the same line using foxit pdf reader
tried googling but couldnt really find an answer
in word i think its just ctrl - click...but this doesnt work in foxit...

anybody know...

More replies
Relevance 45.51%

Hello All, I searched a bit for my scenario, but couldn't find any posts. So posting it here.

I have a
1) HP envy laptop with Windows 8.1 (SSD 750GB Evo). (I pulled out the 1TB HDD it came with and cloned to 750GB SSD. Everything runs fine).

2) I then pulled out the SSD and used the 1TB HDD and installed Windows 7 pro successfully (with all drivers and software required. Everything runs fine).

Now, depending on which OS i want, I am able to swap the drives and can use both SSD or HDD and use their respective OS's 8.1 or 7 PRO (using UEFI/secure boot/disabling it).

Now,I need the convenience of dual booting (so I dont need to swap drives every few weeks). I want to be able to dual boot from these two images. I am planning to create a partition on 750SSD and clone the Windows 7 pro from 1TB HDD. (Both OS's with files and softwares are less than 200GB). So space is not a constraint here.

But I want to know:
Can I copy a clone/image into Second parition (with all system and os partitions from original drives and create a dual boot scenario (while leaving the host 8.1 and its recovery partition in tact). Please note, I AM NOT LOOKING FOR NEW INSTALLATION FOR DUAL BOOT. I ALREADY HAVE 2 PERFECTLY WORKING IMAGES/PARTITIONS OF 8.1 and 7 PRO in separate disks. I JUST WANT TO COPY THE PARTITION(S) FROM ONE DISK TO ANOTHER AND BE ABLE TO DUAL BOOT. I am going to try this now . but please let me know if any of you have tried this before.


Answer:Creating dual boot on SSD, from 2 distinct OS system images

To dual boot both Windows 8.1x64 & Windows 7 x64 will need to installed in UEFI mode.

3 more replies
Relevance 45.51%


I know IE6 is over and out and I'm offcourse running IE8, but as I remember IE6 had one great advantage:

I'm a graphic designer/web designer and in our company we use many external libraries for graphics, HTML coding and so on. These I download when I need them and at the same time I have a lot of open windows in FrontPage, Creator, and especially in IE to see the work I'm doing.

What bothers me, is when you have, let's say 25, Internet Explorer windows open, you can't distinguish a file being downloaded from a IE webpage. This leads to clicking bunches of windows before coming to a window with webcontent. In IE6, as I remember, the "content windows/webpages" had the IE logo/icon in one blue color and the downloading files had the logo/icon in a darker blue color - easy to figure out!

Sadly this has gone since IE7, is there any way to fix this !?

Thanks a lot


More replies
Relevance 45.51%

IGNORE: Stupidity got the better of me; I didn't count how many fans I plugged in... turns out I hadn't plugged it in xD

I think my title suggests my dismay xD

After buying a coolermaster HAF 922 and spending some time getting the set up neat and tidy, checking and double checking all the connections (My first case swap/PSU install etc, went really well!) I powered it on

Obviously I watched the screen and the fans inside; on screen we got full systems a go, windows booting up, all the fans started turning and joy was had!

But I have no LED

I bought if from OCUK and obviously it had an LED, the reviews/specs and whatnot and the picture all say it does in the front panel. But I'm not seeing what I've plugged in wrong or not connected right? It's probably obvious but I'm not seeing it :-/

Lots of love for help!


More replies
Relevance 45.51%

Hi all,

The duplication of results in the Cost Report only happens when in the Course_Details table, in the Course_ID field, there is two StartDate.

Meaning to say, there wouldn't be any problem when generating the cost report when there is only 1 BCF2 in the Course_Details table, with the start date of 24/03/2008. It has 7 related records in Stud_Attend_Courses table. So the cost report will correctly show the 7 records as results.

However, once I added in another BCF2 with the start date of 24/09/2007 in the Course_Details table, the cost report will show the 14 records. There is a duplication.

Attached is my DB. Hope there is a method to solve this problem. Thanks a lot!!!!

Answer:How to stop repeat of results in report, despite using Select Distinct...

15 more replies
Relevance 45.51%

I need to know how to send texts to more than one person.

Answer:how do I send a text to multiple recipients?

Open a text message, choose first recipient then you will see a semi colon after that address. Add another

more replies
Relevance 45.51%

My wife has an android phone and when she sends me a text I receive it multiple times.

Answer:Why do I get multiple copies of the same text message?

I'm afraid that's up to the phone of your wife, not yours. Or potentially on the side of your mobile service provider. There's no known issue or anything that could potentially be the cause of something like this, unless the text is specifically sent multiple times. It does sound like one of those specific "android issues", but don't rule out her mobile service provider!

3 more replies
Relevance 45.51%

Hello, I would like to check that the contents of a single cell contain all of the contents from a number of other cells. Here is an example.Sheet 1 A1 = 111]222]333Sheet 2 A5 = r4]r5Sheet 3 A1 = 111]222]333]r4]r5in Sheet 4 cell A1 the formula is basically going to say if sheet3!A1 contains the contents of Sheet1!A1 and the contents of Sheet2!A5 then "pass" else "FAIL"This formula will be copied from column to column in sheet four so that the relative column reference keeps moving. So Sheet4 B1 says if Sheet3!B1 = Sheet1!B1 and Sheet2!B5 and so on and so forth.Thank you for looking at this issueDranoel

Answer:Multiple Cell text comparison

Hi,Put this formula in A1, then drag as required:=IF(AND(Sheet1!A1="",Sheet2!A1="",Sheet3!A1=""),"",IF(Sheet3!A1="","Empty",
FIND(Sheet2!A1,Sheet3!A1,1)>0),"Pass","Fail")))Note that the formula has been split onto three lines for ease of viewing. Reassemble into one line for use as a formula.If the cell in sheet 3 is empty but there is something in the relevant cell in sheet 1 and/or sheet 2 this formula returns "Empty" as a warning. (You can change this to Fail if preferred).If the cells in sheets 1, 2 and 3 are all empty the formula returns nothing "".Regards

5 more replies
Relevance 45.1%

I want to set up something that will tell me (preferably by email) when someone hits a distinct URL
on my WEB server. Say I give out a WEB site for an issue I'm woring on.
In it I may say something like "For screen shots I have .jpg at

I want to know if they actually went out to that site.  I'm currently using
Microsoft Internet Information Systems, version 6 build 9200, Windows 8.1 enterprise
and it does log this. In my log file,it will have:

2017-04-09 23:37:08 GET /Challenger_rtsp/FL0.jpg - 80 - Mozilla/5.0+(Windows+NT+10.0;+WOW64;+Trident/7.0;+rv:11.0)+like+Gecko
200 0 0 2521

I can see when, where and even who actually went to the site.

I have not seen any feature in IIS that does this. One way would be is if 
I could call wingrep from a .bat file and pass it parameters to search on,
if found send me an email, I don't think that is possible. 
If I knew .NET, (I do Not),  write a script to search each .log file from IIS for a string.
I could use task scheduler to set it up to run regularly.

Any suggestions?

AJ Anning

More replies
Relevance 45.1%

I have a query who's results look like this:

Year ProjectID:
2011 1
2011 1
2011 2
2012 3

The goal is to make a new Access query based on this query that tells me how many UNIQUE project ID's fall under each year. So for the example above, the target query would give me this answer:

Year IDCount:
2011 2
2012 1

Thanks in advance.

Answer:Solved: Access 2007 - Count Distinct Grouped By Year

You can do this using the Gouping fuction (Ribbon>Show/Hide>Totals).
Set the current query Property to Unique Values = Yes, to find the Query Propertiesin design view click on the background under the row grid.
Create the new query based on the original one and set the Year to Group By and the ID to Count.

See the attached database for an example.

3 more replies
Relevance 45.1%


I have a spreadsheet with an amount column(F) and a cost code column(I).

I would like to create some sort of formula that will find all distinct cost codes and list them in another part of my worksheet and sum the amounts for that cost code and place this in the cell beside it. Is this possible and if so how do I go about doing it?


Answer:Excel: Finding Distinct Cost Codes And Totalling The Amount For Each

assuming in my example the rows are rows 2 to 20

in the column you want to place all the distinct cells say K

=INDEX($I$2:$I$20, MATCH(0, COUNTIF($K$1:K1, $I$2:$I$20), 0))

Copy the array formula.
Double click cell K2. (you need to leave cell row 1 clear
Paste (Ctrl + v).
Press and hold Ctrl + Shift.
Press Enter.
Release all keys.
you should see { appear around the formula for an array
and copy cell K2 down as far as necessary

Then in L2 put
and copy down

see my example sheet attached
as described above

1 more replies
Relevance 45.1%

I have a column that has multiple text values within it.

7864 0 99.4% 48 0 7912 402:50:16
199 0 98.5% 3 0 202 23:41:17

I would like to be able to separate each value into a separate column. After a bit of research I was able to find a parsing statement that would work for splitting up first and last names but I was unable to split up more than two values.

FirstName: Right$([Name],Len([Name])- InStr(1,[Name],",")-1)

LastName: Left$([Name],InStr(1,[Name],",")-1)
Does anyone have any idea on how to successfully split up more than two values?

Answer:MS Access: Parsing Multiple Text From Column

It's not clear what you want with the results. You want them in other columns? Or variables? Is this a one-time need, or do you have to do it regularly? Some mor info is needed here, because there are many possibilities, and the objective determines the method.

If the operation is needed to be done only once, and the separated values go into new columns of the table, then the most simple way is:
- Copy the multiple text column via clipboard to an excel sheet
- Use excel's "Text to columns" wizard to separate it. It's in the Data menu.
- Put the results in a new workbook, and save it as "something.xls".
- In Access, open the table you want the data imported into.
- Create the appropriate fields (columns).
- Import data into the table. File/Import, as file type select *.xls, browse to "something.xls", click Import, follow wizard.

If this is not what you want, well, almost any Access wish is satisfiable via VBA code, or perhaps other ways, too, but we need to know, what exactly you want.

3 more replies