Over 1 million tech questions and answers.

Excel 2010 copying multiple sheets into multiple sheets

Q: Excel 2010 copying multiple sheets into multiple sheets

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.

RELEVANCY SCORE 200
Preferred Solution: Excel 2010 copying multiple sheets into multiple sheets

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel 2010 copying multiple sheets into multiple sheets

Read other 7 answers
RELEVANCY SCORE 162

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.
 

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

Hi

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
Code:
[SIZE=1]Sub SortSheets()[/SIZE]
[SIZE=1][/SIZE][SIZE=1]'[/SIZE]
[SIZE=1][/SIZE][SIZE=1]' Macro1 Macro[/SIZE]
[SIZE=1][/SIZE][SIZE=1]'[/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

Read other 2 answers
RELEVANCY SCORE 135.2

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!!!

Regards,
KBM
 

Read other answers
RELEVANCY SCORE 132.8

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
-sht1(main)
-sht11(value)
-sht111(value1)
-sht1111(value2)
etc until
-sht111111111111111111111111(valuex)

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
Loop

Email me
 

A: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...
 

Read other 1 answers
RELEVANCY SCORE 132.8

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

A:Multiple Excel sheets, but only 1 taskbutton

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

Read other 2 answers
RELEVANCY SCORE 132.8

Hi

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
 

A: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.
 

Read other 1 answers
RELEVANCY SCORE 131.2

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!\

Melissa
 

A: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.
 

Read other 2 answers
RELEVANCY SCORE 131.2

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:

Worksheets("January").Activate
ActiveSheet.Protect
*** 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.

Thanks!
 

A:Excel Macro to protect multiple sheets

Read other 8 answers
RELEVANCY SCORE 131.2

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


Code:
cls
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


$svr
$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.

Read other answers
RELEVANCY SCORE 131.2

Hello,

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?
 

A: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.

Thanks!

Sub AllSheets()

Dim Y As Integer
Y = 1

Do While Y < 22
Sheets(Y).Activate
Call test
Y = Y + 1
Loop

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

Read other 1 answers
RELEVANCY SCORE 130

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.
 

A:Solved: EXCEL - Consolidate data from multiple sheets

Read other 11 answers
RELEVANCY SCORE 130

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

A: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!
 

Read other 2 answers
RELEVANCY SCORE 130

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.
 

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

Read other 16 answers
RELEVANCY SCORE 130

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?

Thanks
 

A:Access:OutputTo function and multiple Excel sheets

Try something like this using the TrasferSpreadsheet function.

http://www.access-programmers.co.uk/forums/archive/index.php/t-76646.html

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

Read other 2 answers
RELEVANCY SCORE 130

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.
 

A: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.

Regards,
Rollin
 

Read other 1 answers
RELEVANCY SCORE 128.4

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.
 

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

Read other 14 answers
RELEVANCY SCORE 128.4

hi

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

thanks
 

A: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.

lol
Hew

unless someone has a better idea???
 

Read other 3 answers
RELEVANCY SCORE 128.4

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!

A
 

Read other answers
RELEVANCY SCORE 128.4

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.

CREATE TABLE PS_NFS_HOSPITAL_MS
(
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
etc.
)
TABLE PS_NFS_HOSPITAL_MS
 

Read other answers
RELEVANCY SCORE 125.6

G'day,

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!
 

A: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.

lol
Hew
 

Read other 3 answers
RELEVANCY SCORE 118.8

Hi friends.
New to this wonderful forum.
May I request to help me.
I have,.. some 300 sheets in a Workbook, having 40 rows each.
Is it possible to copy all cells/data in one sheet, if yes, please help me.
As of now, I use the formula, like
(on target sheet
=sourcesheet!CellAddr

and then copy this formula to get the result.

Thanks
 

A:Excel - Copying cells from other sheets

See ASAP's "Put together rows or columns from several sheets".

http://www.asap-utilities.com/
 

Read other 1 answers
RELEVANCY SCORE 117.6

I have an excel file with 5 sheets. Sheets 1 to 4 have rows of data, sorted under various columns, out of which one column in each sheet is titled "priority" and every row has 'high', 'medium' or 'low' populated in the priority column. I need to extract all 'high' priority items and automatically copy those entire rows into sheet 5 automatically, either real-time, or by pressing an update button or something in sheet 5.

Can anyone tell me how it can be done, either through a macro or a simple 'if' 'then' condition.

Thanks
 

A:Excel - automatic copying of rows between sheets

Read other 8 answers
RELEVANCY SCORE 117.6

Hello,

I need to sort extensive data and am facing the following problem:

There are two sheets: Sheet1 and Sheet2

Columns in Sheet 1 are the following:
company PERMNO number, dates, market capitalization (no data in this one)

Columns in Sheet 2 are the following:

company PERMNO number, ticker, dates, market capitalization

In Sheet2, there is a whole range of dates (between 1990 and 2004) for every single company, whereas in Sheet2 there is one or 3-4 dates for a every single company.

I would like to match the exact date/dates in Sheet1 in the range of dates in Sheet2 and then if they match, copy the corresponding cell value from the market capitalization column in Sheet2 to the empty market capitalization column in Sheet1. I also want that the company PERNO numbers match.

In short: if PERMNO numbers match, match the date/dates in Sheet1 within the range of dates for the same company in Sheet 2 and copy the market capitalization value to Sheet1.

Example:

Sheet1:
A B C
1. PERMNO DATES MARKETCAP
2. 13123 199803
3. 13123 199904
4. 65456 200005
5. 44550 200104
6. 44550 200211
7. 44550 199601

Sheet2:
A B C
1. PERMNO DATES MARKETCAP
2. 13123 199803 8900000
3. 13123 199804 7767575
4. 13123 199805 7567657
..............
.............
99. 13123 200412 7999999
100. 65456 199803 4141241
101. 65456 199804 4145331
101. 65456 199805 4145551
............
............
200. 65456 200412 4458888

When we match the date from sheet1 - 199803 and 199904 within the range from She... Read more

A:Need help with excel VBA - matching and then copying data from two sheets

Off the top of my head, I would think all you need to do is use the built-in "find" function. Look it up. Make sure sheet 2 is sorted before you use the find.

For each PERMNO in sheet1, do the find function on sheet2. When it finds a match it returns the row of the FIRST match in sheet2. At this point, loop through the rows in sheet2, starting on the row that the find function returns, until you hit a PERMNO that is not the one you are looking for. This will work only if sheet2 is sorted. On each of those iterations you can check for the matching date, and on that match you can do the copy of the marketcap.

In VBA, you have to be very careful about which sheet you are referencing. So make sure to always put sheet1.cells(3,4) instead of just cells(3,4).

Hope this helps somewhat.
 

Read other 3 answers
RELEVANCY SCORE 112.4

hi
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
regards
 

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

Read other 2 answers
RELEVANCY SCORE 111.2

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.

thanks
 

Read other answers
RELEVANCY SCORE 111.2

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.
i.e
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

A:Solved: count values across multiple sheets

Read other 16 answers
RELEVANCY SCORE 111.2

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.
 

Read other answers
RELEVANCY SCORE 111.2

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
 

A:count unique values across multiple sheets

answer at this link here...
 

Read other 2 answers
RELEVANCY SCORE 111.2

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
 

A: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.
 

Read other 3 answers
RELEVANCY SCORE 110.8

I've tried goggling this a bit and can't seem to find exactly what I'm looking for.

I'm trying to setup a sheet that can run calculations based upon what someone else enters (ex: if they select 'option1', preferably from a drop down list, then two values will be set to the corresponding values, ex: '5' and '6' or whatever they may be). This would preferably actually be within a formula, so say if they have selected an option, it will perform the calculation with the appropriate data, without having to display it on the current sheet.

I was also trying to keep all the data on another sheet so the one I was actually using looked a bit more organized. I've never had to do anything like this in excel before and was looking for some help or a push in the right direction.

A:Excel 2010 - Accessing data based on conditions from other sheets

I have found the 'CHOOSE' function very useful.
Very difficult to explain, but here is Microsoft Office attempt... CHOOSE function - Excel - Microsoft Office

My scenario was to have a cell in which you would select Quarter 1, 2, 3 or 4. The values in the Profit & Loss Account would change accordingly. Here are examples of my scenario (the cells highlighted in the spreadsheet on the right refer to another sheet - CY Actual - in the workbook).

I hope this helps.

Read other 2 answers
RELEVANCY SCORE 110

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)

=DCOUNT([123.xls]Data!$B:$F,"Result",C$1:E2)+DCOUNT([345.xls]Data!$B:$F,"Result",C$1:E2)+DCOUNT([789.xls]Data!$B:$F,"Result",C$1:E2)

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:

=DCOUNT(D*!$B:$F,"Result",C$1:E2)

Any help would be greatly appreciated!!

Chris
 

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

Read other 7 answers
RELEVANCY SCORE 110

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

A: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.

Jack
 

Read other 2 answers
RELEVANCY SCORE 110

Hello..

type
xyz
xyz
xyz
ccc
ccc
ccc
ccc
ccc
ccc
zzz
zzz
zzz
zzz

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
 

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

Read other 6 answers
RELEVANCY SCORE 108.8

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

A: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.
 

Read other 3 answers
RELEVANCY SCORE 108.8

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.

Or

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:

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

Read other answers
RELEVANCY SCORE 107.6

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)

AND

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

A: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?
 

Read other 2 answers
RELEVANCY SCORE 93.6

Is there a formula or macro I can use to add all the cells C4 in each of the several sheets? I am thinking of something like in the old Lotus 123 Release 3. A simple syntax might be "Sum(Sheet1!C4:SheetN!C4"). Does anyone know how to use Excel as a true 3D spreadsheet?
 

A:MS Excel Sheets

Read other 8 answers
RELEVANCY SCORE 93.6

I've got a macro, that I want to modify so that it reads the one worksheet, then makes global changes to all the other worksheets. I only know how to get it to update the single sheet specified sheets(2), but don't know how to get it to do updates to all the sheets.

Code:

Sub test()
Dim lrow As Long, i As Long
With Sheets(1): .Activate: Application.ScreenUpdating = False
lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lrow To 2 Step -1
If Cells(i, 1) <> "" Then
Sheets(2).[b:z].Replace What:=.Cells(i, 1), Replacement:=.Cells(i, 2), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True: With Cells(i, 1): .Value = Cells(i, 2)
.Interior.ColorIndex = 35: End With: End If
Next: End With: End Sub

 

A:How to look through sheets in Excel

Hi welcome to the board:
you can loop through all the sheets

Code:

Dim ws as worksheet
For Each wS In ActiveWorkbook.Worksheets
< your code here>
next ws

You will probably have to include a check to see if its the sheet you nedd but since you can code some thing I imagine you'll be able to work with the code above?
 

Read other 1 answers
RELEVANCY SCORE 93.2

I was printing a doc when half way through, I started getting blank pages. The printer is an HP Laser jet with a new cartridge.

I switched over to my Canon laser printer - same problem.

I cut and paste the doc to new doc - same problem.

I put the doc on my other PC with a Brother laser printer - same problem.

Evidently it is a Word problem not a printer problem.

All three printers printed the pages numbers only.

I have read that reinstalling Word does not work.

Does anyone know to how to fix this problem?

Thanks - reasoner

A:Word 2010 printing blank sheets

It is unusual that it should be happening on all three printers.
Is this a new install of Word 2010?
Is the Cannon networked to the machine that has the HP?

Check to make sure "Print only the data from a form" is un-checked in Words Options.

If that does not help, what I would like you to do is, go into each of the three printers properties, and click on the Advanced tabs, but lets start with the HP Laser jet since that seems to be your #1 printer.

The usual procedure is:
Control Panel >Printers & faxes.
Right click printer, click properties, click Advance tab.
Remove default check on "Spool print documents so program finishes printing faster.
Check "print directly to the printer".
See if that helps.
This could be a .dll file or spooling problem.

I have spooling checked, and print directly unchecked on my machine, and I have switched from Lexmark to HP Photosmart with no problems so far.

Read other 9 answers
RELEVANCY SCORE 92.4

it may sound dumb, but i use excel on my pocket pc often. one of the things i really find lacking is the letter tabs found on most contact software. i'm not sure what they're called, but they are at the top of a long list of contacts, usually the first row, and they contain the alphabet, grouped abc in the first tab, and def in the next, etc. i want to port this feature into excel. i have excel sheets with more than 50 names in column A, and i need to scroll all the way down to access the names beginning with the higher letters of the alphabet. in order to save time, i'd much prefer the little letter tabs (i wish i knew the correct name), so i could just click the appropriate tab to quickly bring me to the names beginning with that letter. for example, if i wanted to bring up Mrs. Tanner, i could just click the 'T' tab, and i would be brought to all the names beginning with T.
 

A:can i add a 'letter' tab to excel sheets?

Read other 16 answers
RELEVANCY SCORE 92.4

I work from an excel spread sheet daily. This spread sheet has about 4,000 lines. When I delete lines in this spread sheet I get a gray box with the sum symbol in it. No matter what I do, I can't get this to NOT "automaticly appear". If I print the document, it prints. If I move the symbol, it copies and keeps a copy where it began. If I copy a cell it copies with the cell. I tried deleting the symbol and yes it deletes however there are hundreds underneath it which means I am deleting hundreds. How can I delete all of these at one time, or better yet stop this? It really is annoying.
 

A:Excel Spread Sheets

Hi,

Not sure what you have for those boxes, but if they are a shape or OLE control - which a shape is - you may make use of this macro to rid all shapes/OLE objects from your sheet...
Code:
Sub delAllShapes()
For Each Shape In ActiveSheet.Shapes
Shape.Delete
Next
End Sub

or

Sub delAllObjects()
For Each s In ActiveSheet.OLEObjects
s.Delete
Next
End Sub
There's 2 and they should both work just fine. To run, copy code. From Excel press Alt + F11, select workbook in left frame (if not there Press Ctrl + R {xl 2002} or Project Explorer from the View menu), go to Insert -> Module, paste code in right window. Call by (first closing that window, the Visual Basic Editor {VBA}), then press Alt + F8, choose the macro name to run and press 'Run'. HTH
 

Read other 1 answers
RELEVANCY SCORE 92.4

Hi,

I have a template that I use every day for every month of the year. Each month I copy the workbook and rename it with the appropriate month and then go in and change every single tab (worksheet) name to correspond to the individual days of the month. For example, for April, I took March's workbook and saved it as April and went in and changed all the sheets to correspond with the days of April.

Is there a way or some type of macro someone could help me with that would copy the appropriate number of worksheets and name them according to the appropriate days for that month??? I was thinking of using an Input Box to determine the month to generate for.

Thanks for reading!
 

A:excel workbooks and sheets

Read other 6 answers
RELEVANCY SCORE 92.4

Is there a VBA Macro to alphabetize multiple sheets in an Excel single Workbook or other code? I do timecards for my employees and someone helped me with a macro that is amazing from a template page. Once transfered though I send it in and it's like 25 people and they need to be alphabetized. I see some people say they can online but what I have tried has not worked and I spend a lot of time just alphabetizing sheets when I would assume a programmer would have figured out how to automate that. Essentially I feel like I'm doing too much unnecessary work. Anyone know a code, filter, or maybe simply an edit tool I never knew about in Microsoft Excel 2003 or 2007? Any help is much appreciated, thanks.
 

A:alphabetize sheets in excel?

Read other 6 answers
RELEVANCY SCORE 92.4

I can't link to a cell in another sheet in the same workbook (as in '=target'). I can link to another cell in the same sheet, but not to another sheet. This function works okay on my work computer, but not on my laptop. Seems like it's a setting in excel. Has anyone experienced this problem before?
 

A:Excel won't link between sheets

No havent experiened that. I have a laptop and don't get that problem.

On your laptop,can you actually type the = in a cell, and then click on the tab of a different worksheet within the same workbook and then click on a cell and press enter?

If not, then can you upload the workbook using your laptop, obviously removing any sensitive data, so that we can have a look and see if we can see what the problem is.
 

Read other 1 answers
RELEVANCY SCORE 92.4

Here is my situation

I am working a workbook with 78 different sheets (tabs). The format for each tab is the same. Rows 1-4 are header information that is unique to each tab.

row 5 is my column titles (same for all tabs)

rows 6-??? (varies on each tab) is my data (unique data on each tab)

I am trying to devise a way to pull in all of this information combined where it will list the unique header information for each tab along with the information from rows 6 on. Once in a sheet, I would like to set this up in a pivot table because some of the data from rows 6 on may be the same on different tabs, so I would like to see that combined, but also be able to expand to see which tab each piece of info came from.

I know this is complicated, but I have been reading through the forums and if this can be solved it will be by the knowledgeable people in this forum.
 

Read other answers
RELEVANCY SCORE 92.4

cannot delete extra sheets in excel document..i have 2 pages of data, and page break shows 2 pages, i go to print preview and there are 4 pages; and page 2 and page 4 are blank....help!!
 

A:excess sheets in excel

in print preview you should see some dotted lines - I would goto the column on the right with the last data entry and then select the next few columns and delete those columns - see if that resolves the issue

what version of excel

also look at the print area used ?
and set that area to the pages you want printed
 

Read other 3 answers